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: 
steve_br
Creator
Creator

Comparing periods

Hello!

I need some help.

I would like to create KPI objects that compare costs, volume, and distance and are filter sensitive. So basically, if January, February, March and April are selected, I want the KPIs to compare April vs. March. If April is excluded from the selection and only CountryA is selected, then the KPIs should compare March vs February for CountryA, etc. So something like this would be great:

Untitled.png

I did read a lot of similar topics here, but I never managed to come close to what I want.

This is a sample of the data I am working with.

CountryDateDriverCostsVolumeDistance
CountryA05.01.2016Driver133517220298
CountryA05.01.2016Driver126418413265
CountryA05.02.2016Driver126715775240
CountryA05.02.2016Driver238416897239
CountryA05.02.2016Driver222921664175
CountryA05.03.2016Driver233516550148
CountryA05.03.2016Driver333317114143
CountryA05.04.2016Driver321618841287
CountryA05.04.2016Driver329316971196
CountryA05.04.2016Driver329420533339
CountryB05.01.2016Driver430118949149
CountryB05.01.2016Driver420716415272
CountryB05.02.2016Driver425019991162
CountryB05.02.2016Driver528820089226
CountryB05.02.2016Driver528519417113
CountryB05.03.2016Driver522220062133
CountryB05.03.2016Driver528915887202
CountryB05.04.2016Driver623820430211
CountryB05.04.2016Driver635920099155
CountryB05.04.2016Driver623421839297
CountryC05.01.2016Driver733217022288
CountryC05.01.2016Driver724016451217
CountryC05.02.2016Driver731318548134
CountryC05.02.2016Driver839016733292
CountryC05.02.2016Driver831119139251
CountryC05.03.2016Driver833521726230
CountryC05.03.2016Driver923718130340
CountryC05.04.2016Driver927621143348
CountryC05.04.2016Driver936819538184
CountryC05.04.2016Driver938817782122

I would appreciate any help!

1 Solution

Accepted Solutions
sunny_talwar

Like this:

Capture.PNG

Expressions Used

Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date))) & '<=' & Date(MonthEnd(Max(Date))))"}>}Costs)

Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date), -1)) & '<=' & Date(MonthEnd(Max(Date), -1)))"}>}Costs)


Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date))) & '<=' & Date(MonthEnd(Max(Date))))"}>}Volume)

Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date), -1)) & '<=' & Date(MonthEnd(Max(Date), -1)))"}>}Volume)


Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date))) & '<=' & Date(MonthEnd(Max(Date))))"}>}Distance)

Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date), -1)) & '<=' & Date(MonthEnd(Max(Date), -1)))"}>}Distance)

View solution in original post

3 Replies
sunny_talwar

Like this:

Capture.PNG

Expressions Used

Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date))) & '<=' & Date(MonthEnd(Max(Date))))"}>}Costs)

Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date), -1)) & '<=' & Date(MonthEnd(Max(Date), -1)))"}>}Costs)


Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date))) & '<=' & Date(MonthEnd(Max(Date))))"}>}Volume)

Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date), -1)) & '<=' & Date(MonthEnd(Max(Date), -1)))"}>}Volume)


Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date))) & '<=' & Date(MonthEnd(Max(Date))))"}>}Distance)

Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date), -1)) & '<=' & Date(MonthEnd(Max(Date), -1)))"}>}Distance)

vinieme12
Champion III
Champion III

Period Presets: Compare Periods on the fly

if you do this in your script using presets, your expressions will then be simplified to something as below.

CurrentMonth = Sum({<Period= {$("=MAX(MONTH)"}>}Costs)

Previous Month = Sum({<Period= {$("=MAX(MONTH)-1"}>}Costs)




Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
steve_br
Creator
Creator
Author

Thank you guys for the help! I did it as Sunny suggested, it works exactly as I wanted. Awesome!

Thank you again!