Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
raxlifaxli
Contributor II
Contributor II

Calculate across different periods

Hi

I have the following table

 

Calendar Month/YearPYCYTurnoverFactor
2018.011 2254
2018.021 2754
2018.031 2502
2018.041 1802
2018.051 3204
2018.061 1402
2018.071 5202
2018.081 4304
2018.091 4104
2018.101 3002
2018.111 5256
2018.121 4504
2019.01 13003
2019.02 11004
2019.03 12305
2019.04 11206
2019.05 15003
2019.06 12202
2019.07 11257
2019.08 13506
2019.09 14008
2019.10 12006

 

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

Labels (3)
5 Replies
mohamed_ahid
Partner - Specialist
Partner - Specialist

hello

test this

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

raxlifaxli
Contributor II
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 .

Test.PNG

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

Cheers

Brett_Bleess
Employee
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.

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

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.
raxlifaxli
Contributor II
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

Brett_Bleess
Employee
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.