cancel
Showing results for
Did you mean:
Contributor II

## Calculate across different periods

Hi

I have the following table

 Calendar Month/Year PY CY Turnover Factor 2018.01 1 225 4 2018.02 1 275 4 2018.03 1 250 2 2018.04 1 180 2 2018.05 1 320 4 2018.06 1 140 2 2018.07 1 520 2 2018.08 1 430 4 2018.09 1 410 4 2018.10 1 300 2 2018.11 1 525 6 2018.12 1 450 4 2019.01 1 300 3 2019.02 1 100 4 2019.03 1 230 5 2019.04 1 120 6 2019.05 1 500 3 2019.06 1 220 2 2019.07 1 125 7 2019.08 1 350 6 2019.09 1 400 8 2019.10 1 200 6

I want to calculate the monthly turnover with the average of factor of the previous year (indicator PY). I use the formula

Sum(Aggr(Sum(Turnover), [Calendar Month/Year])) * Sum(Aggr(Avg({1<PY={'1'}>} Factor ), PY))

But it does only work for the first month  2018 not for any other months . Also it should show the  figures if an selection on the months is done for the selected months. Can anybody help me?

I attached an example app.

Labels (3)

• ### Set Analysis

5 Replies
Partner - Specialist

hello

test this

Sum(Aggr(Sum(Turnover), [Calendar Month/Year])) * Sum(Aggr(Avg({1<PY={'1'}>} Factor ), PY,[Calendar Month/Year]))

Contributor II
Author

Hi

it seems not to work. Adding the calendar month as an aggregation level does not return the previous year average but a monthly value.  In addition, it calculates only for the previous year and not for the current year .

Seems I Need to Change the data model.  But thanks very much for looking into.

Cheers

Former Employee

Here is a link to a You Tube video Henric Cronstrom has done related to the QIX Engine and how things work in three of the seven modules that I think may help explain what you have going on, but I am not going to be able to try to explain it, it will be better to watch the video as hopefully things will click after that, but I believe the other poster was on the right track from what I know, pretty sure it is aggregation issue and hopefully the video may get you what you need in this case.

Sorry I am not more help, hopefully this will help you move things forward.  You can probably skip ahead to the part where he talks about how things impact Set Analysis, but I suspect it will be most helpful to watch the entire thing.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Contributor II
Author

Thanks for sharing this link. Although this is not a solution for My Problem I found this very helpful and interesting.

Thanks very much

Former Employee

The only other thing I can offer is the following links from Design Blog area, and the last one is the base URL, so if you want to search yourself there, you can do so, hopefully one of these may be of further help in explaining things:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

The first link post does have a couple of related links at the bottom, just FYI, sorry I am not more help to you, but hopefully something out here may help explain why things are doing what they are doing, there are some posts on dates and set analysis too, just FYI, if you search on set analysis from the 2nd link, you should see all the topics there related to that.  Again, apologies I do not have something better.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Community Browser