locked
Aggregate using Lookup function RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Is there any way to get a sum or average of data from a dataset using a Lookup function?

    I have daily currency data, and want to use a lookup function to get monthly averages - at this stage, the lookup function will only return the data for the first entry of the month.

    Thanks

    Wednesday, February 18, 2015 12:16 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi MIDASuer,

    Per my understanding that you want to get the sum or monthly average value based on the daily currency data, right?

    I have tested on my local environment and I assumed that you have an date/time type field in the table, if so, we can get the month value based on the daily date and then create row group based on the month, finally we can use the avg or sum function to get the monthly average values.

    Detail information below for your reference:

    1. We can modify the query in the dataset to get the month value from the date:
      SELECT   Amount, Date, DATEPART(month, Date) AS Month
      FROM      TableName
    2. Add an row gorup of the month(Month)
    3. Use expression below to get the average value of the month and the sum of the month:
      Monthly Average: =Avg(Fields!Amount.Value,"Month")
      Monthly Sum :  =Sum(Fields!Amount.Value,"Month")
    4. If the amount value comes from another dataset using the lookup function, you can use expression below o get the monthly average:
      =Avg(Lookup(source_expression, destination_expression, result_expression, dataset),Month")
    5. preview like below:

    More details information about the LookUp function for your reference:
    https://technet.microsoft.com/en-us/library/ee210531(v=sql.110).aspx

    If your problem still exists, please try to provide us some sample data and more detail information about your requirements.

    Regards,
    Vicky Liu

    Vicky Liu
    TechNet Community Support


    • Proposed as answer by Vicky_Liu Monday, February 23, 2015 6:06 AM
    • Marked as answer by Vicky_Liu Tuesday, February 24, 2015 5:54 AM
    Wednesday, February 18, 2015 2:17 PM