How I solved my Last 12 months revenue Comparison Problem

Rai Qaiser Hussain

Hi All

I just want to share my experience with all of you, how I solved my problem of last 12 months revenue comparison, I tried different methods but liked this one, my purpose is just to share my experience, maybe helpful to someone

Fig.1, Added a new column in Time dimension with format YYYYMM

Fig.2, Added column in RPD

Fig.3, Added in Presentation layer

Fig.4, Presentation variable created on prompt

Fig.5, Result of Prompt

Fig.6, Added Filter of this field on Analysis report, Presentation variable and default value added.

Fig.7, Added Prompt and analysis on my dashboard, following is the result (some data erased on image)

regards

[Deleted User]

Edit: Never mind I just saw the "-99".

Also a way to do it. Personally I would prefer going for encapsulated out-of-the-box functionality which can be used by all users in exactly the same fashion and doesn't need to be reproduced for each use case.

Saresh-Oracle

Hi Rai Qaiser Hussain,

Thanks for sharing this.

Regards,

Sareesh

Gianni Ceresa

I would adapt the filter as "functionally" it has a mistake:

"Time"."Month Short Name" >= (@{pv_MonthShortName-99}{201801}) AND "Time"."Month Short Name" <= (@{pv_MonthShortName}{201901})

=> if no variable it will return 13 months and not 12

For better readability and chances somebody will understand what's going on in the future the '-99' would need to be outside of the variable to keep the logic coherent between default values and variable:

"Time"."Month Short Name" >= (@{pv_MonthShortName}{201901} - 99) AND "Time"."Month Short Name" <= @{pv_MonthShortName}{201901}

(a BETWEEN can also be used instead of 2 filters)

The logic of this approach is purely mathematical: by expressing months as 6 digits YYYYMM the -99 act as "go back 1 year and forward 1 month" (-100 + 1).

Because of the numerical implementation the border case of December still works ( YYYYMM BETWEEN 201713 AND 201812 when selecting December 2018).

It works, sure, as long as nobody asks to see months in a different format in the prompt (like "Jan 2019" or things like that). There are various other implementation for that kind of "period rolling" requirement.