Calculate across different periods

[raxlifaxli]

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.   

Thanks in advance for your help

[mohamed_ahid]

hello

test this

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

[raxlifaxli]

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

[Brett_Bleess]

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.

https://www.youtube.com/watch?v=wevhFK_AID8&feature=youtu.be

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

[raxlifaxli]

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

Thanks very much

[Brett_Bleess]

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