set analysis on dimension inside AGGR()

[ArchanaB]

Hi ,

How Can I apply set analysis to Dimensions used inside Aggr function . My formula is below :

aggr(RangeSum(ABOVE(Sum({<$(vSet),HACKETT_TEVA_SAVING={$(vExpFilterByHACKETT_TEVA)}>}SAVINGS_ACTUAL),0,RowNo())),SAVINGS_CATEGORY,ACTUAL_DATE)

I want to apply $(vSet) in set analysis to Actual_date dimension as well . I tried using it but it doesnot give me any output.

[sunny_talwar]

Not entirely sure, but it seems that you already have used it within the Sum() function. May be you need to add the same set to your expression too

[ArchanaB]

Actually I am using it in Bar chart , to display accumulated value of Saving_actual with accumulated . it has two dimension , Month and Saving_category.  The dashboard has filter "Period" . variable $(vset) omits period filter giving me data for whole year. 

Now with this formula , I am getting accumulated chart only for selected period , not for whole year. that is why I am not sure if it is the added set analysis in Rangesum also applies to the Actual_date or not . 

[sunny_talwar]

I think you need to ignore selection in Period from your expression for this to work. What is the expression/measure that you use for the bar chart?

[ArchanaB]

So I was using below measure named (total savings Actual) in bar chart and then doing accumulation in chart it self :

num(sum({<$(vSet),HACKETT_TEVA_SAVING={'HACKETT''>}SAVINGS_ACTUAL),'$(vNumFormat)')

which gives me output like these for dimensions "Month" and "saving_category"

If I remove Savings_category dimension, It will give me out put with all 2019 months .

I want out put with all the 2019 months when "2019-Q4 " selected in period and stacked by category like in previous image. I am not sure why it only gives accumulated period output while only Month dimension is there and not when Saving category is added. The data is available for both Month and Savings category for whole 2019 year. 

[sunny_talwar]

Would you be able to share a sample app so that we can see the issue?

[ArchanaB]

Hi Sunny,

Please find attached sample app. Here if we don't select any period from filter, it gives me months from Jan 2019 till Dec 2019 in chart. But when we select period it only shows the current period months. I want that when we select period , it should display accumulated month in chart. If I remove the second dimension "Savings_category" , it does show me all month even with filter selection, only when I add the second dimension, it is not working when period is selected from filter.

I.e. if selected period=2019-Q3 , the chart should display output for month jan-2019 till sep-2019.

[ArchanaB · Accepted solution]

Hi Sunny,

I fixed the issue. I removed the accumulation from chart instead created the accumulated measure and used in chart. In measure I applied the set analysis over aggr function and it resolved the issue. Below is the expression i created fro accumulation :

max({<$(vSet),HACKETT_TEVA_SAVING= {'HACKETT'}>}

                aggr(RangeSum (Above(sum({<$(vSet),HACKETT_TEVA_SAVING= {'HACKETT'}>}SAVINGS_ACTUAL),0,RowNo()))

               ,SAVINGS_CATEGORY,ACTUAL_DATE))

thank you for all your help  🙂