Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
ArchanaB
Contributor III
Contributor III

set analysis on dimension inside AGGR()

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.

 

Labels (2)
1 Solution

Accepted Solutions
ArchanaB
Contributor III
Contributor III
Author

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  🙂

 

 

 

View solution in original post

7 Replies
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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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)')

ArchanaB_0-1591706960910.png

 

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

ArchanaB_1-1591707036229.png

 

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

 

ArchanaB_2-1591707105791.png

 

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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  🙂

 

 

 

View solution in original post