Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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.
Country | Date | Driver | Costs | Volume | Distance |
CountryA | 05.01.2016 | Driver1 | 335 | 17220 | 298 |
CountryA | 05.01.2016 | Driver1 | 264 | 18413 | 265 |
CountryA | 05.02.2016 | Driver1 | 267 | 15775 | 240 |
CountryA | 05.02.2016 | Driver2 | 384 | 16897 | 239 |
CountryA | 05.02.2016 | Driver2 | 229 | 21664 | 175 |
CountryA | 05.03.2016 | Driver2 | 335 | 16550 | 148 |
CountryA | 05.03.2016 | Driver3 | 333 | 17114 | 143 |
CountryA | 05.04.2016 | Driver3 | 216 | 18841 | 287 |
CountryA | 05.04.2016 | Driver3 | 293 | 16971 | 196 |
CountryA | 05.04.2016 | Driver3 | 294 | 20533 | 339 |
CountryB | 05.01.2016 | Driver4 | 301 | 18949 | 149 |
CountryB | 05.01.2016 | Driver4 | 207 | 16415 | 272 |
CountryB | 05.02.2016 | Driver4 | 250 | 19991 | 162 |
CountryB | 05.02.2016 | Driver5 | 288 | 20089 | 226 |
CountryB | 05.02.2016 | Driver5 | 285 | 19417 | 113 |
CountryB | 05.03.2016 | Driver5 | 222 | 20062 | 133 |
CountryB | 05.03.2016 | Driver5 | 289 | 15887 | 202 |
CountryB | 05.04.2016 | Driver6 | 238 | 20430 | 211 |
CountryB | 05.04.2016 | Driver6 | 359 | 20099 | 155 |
CountryB | 05.04.2016 | Driver6 | 234 | 21839 | 297 |
CountryC | 05.01.2016 | Driver7 | 332 | 17022 | 288 |
CountryC | 05.01.2016 | Driver7 | 240 | 16451 | 217 |
CountryC | 05.02.2016 | Driver7 | 313 | 18548 | 134 |
CountryC | 05.02.2016 | Driver8 | 390 | 16733 | 292 |
CountryC | 05.02.2016 | Driver8 | 311 | 19139 | 251 |
CountryC | 05.03.2016 | Driver8 | 335 | 21726 | 230 |
CountryC | 05.03.2016 | Driver9 | 237 | 18130 | 340 |
CountryC | 05.04.2016 | Driver9 | 276 | 21143 | 348 |
CountryC | 05.04.2016 | Driver9 | 368 | 19538 | 184 |
CountryC | 05.04.2016 | Driver9 | 388 | 17782 | 122 |
I would appreciate any help!
Like this:
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)
Like this:
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)
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)
Thank you guys for the help! I did it as Sunny suggested, it works exactly as I wanted. Awesome!
Thank you again!