Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
grisldopf
Contributor II
Contributor II

aggregation of filter selection for previous years

Hi!

i have a question concerning filters and calculation of previous year values.

basic functinality is that you can select a yearmonth in the filter box and you get the correct values of the selection and the previous yearmonth in the bar chart

i use following code

=(Sum({<[0CALMONTH]={$(=Date(AddYears(Max([0CALDAY]), -1),'YYYYMM'))}>}SALES_1R1))

 

Unfortunately this is working only for one selection. So i you selct more than one yearmonth, the current selection is aggregated correctly, but the respective previous years do net.

How can i change that or how can i aggregate the resprective previous years from the selection.

 

hope my question is clear and someone can help me

 

thanks

br

chris

Labels (4)
2 Replies
bramkn
Partner - Specialist
Partner - Specialist

have you tried the aggr function? sum(aggr(.......,yearmonth))
grisldopf
Contributor II
Contributor II
Author

thanks for your reply

unfortuantely it does not work and i guess its because i am using an expression as dimension

Qlik help says:

"Each dimension in an Aggr() function must be a single field, and cannot be an expression (calculated dimension)."

but is works if i simply take sum(sales) for aggregation, but thats not my intention. I need to sum up all Sales for the year before the multiple filter selections.

So i want to filter 201808, 201807 and 201806 (YYYYMM). 

 

filter.PNG

and i want to have in the chart the sum of all sales of current selection plus sum of all sales of the current selection minus 1 year so 201708+201707+201706

it only works when selecting one timeperiod (e.g. 201708), but not when selecting all three of them

When i select all three time periods i stick to the maximum of timeperiods, but not the sum of it.

chart.PNG