Multiple date selection for KPI

[Sumit2504]

Hi All,

As per my current scenario, my KPI's are displaying (Current - previous month) in %. But users are expecting if they select Multiple months like 3 months(Quarter) and 6 months(Half Yearly) then how i can change the calculation.

For example - If user select Dec21, Nov21, Oct21 then in KPI it should select ((dec21 + Nov21, Oct21) - (Sept21 + Aug21+July21)).

Same case for Half yearly.

Can anyone help here

Regards,

Sumit

[chris_djih · Accepted solution]

Hi @Sumit2504 ,

I kind of got your challenge solved by using some variables (for better overview):
vGetMonthSelectionCount:    =SubStringCount(GetFieldSelections(Date.Month,'|'),'|')+1
This one calculates how many months are selected.
vGetFirstPrevMonth:      =AddMonths(MonthStart(Max(Date)),-2*$(vGetMonthSelectionCount)+1)
vGetLastPrevMonth:      =AddMonths(MonthStart(Max(Date)),-$(vGetMonthSelectionCount)+1)
These two variables give me the dates to form the correct Set-Analysis:
Sum({<Date={">=$(vGetFirstPrevMonth) <$(vGetLastPrevMonth))"}>} [Turnover])

In practice: When I select Jan,Feb,Mar the variables will be calculated as follows:
vGetMonthSelectionCount = 3
vGetFirstPrevMonth = 01.10.2021
vGetLastPrevMonth = 01.01.2022

So the final Set-Analysis resolved to: Sum({<Date={">=01.10.2021 <01.01.2022" }>} Turnover).
Giving me all the turnover between 01.10.2021 and 31.12.2021 which is Okt,Nov,Dez.

[Sumit2504]

Hi @chris_djih ,

Thanks for your response, is there a way that we can automate the number of month selection made by user.

For example, user can select 4 or 5 or 6 months and he can get the data for respective (selection - previous) months?

[Sumit2504]

Hi @chris_djih ,

How to show the range for selected months ?

i.e Sum({<Date={">=01.01.2022 =<31.03.2022" }>} Turnover).

[chris_djih]

What do you mean with automate the number of month selection?

it is aleady fully dynamic, it works if you select 1,2,3,4,5 or any number of months, thanks to the
vGetMonthSelectionCount variable.

to show the selected month simply use GetFieldSelections(Date.Month,'|').

[Sumit2504]

@chris_djih - Thank you for your help and support

[Sumit2504]

@chris_djih - I want to show selected months in set analysis.

I have stored GetFieldSelections(Date.Month,'|') in variable vCurrentMonthSelection. and then trying to use count( {$<Date_LastActivityAt = {$(vCurrentMonthSelection)}>}Id) but it's not showing any results.

Can you please help here

[chris_djih]

Yes, because GetFieldSelections() will generate a string. that consosts of the monthname and the pipe ('|') as seperatpr. Your Date_LastActivityAt field seems to be a date. so you try to compare "01.01.2020" with "Jan|Feb|Mar" -> of cause ther are no results.

id on't excactly understood want you are looking for but i would guess to use the same procedure as for the other KPI: count( {$<Date_LastActivityAt = {">=01.10.2021 < 31.12.2021"}>}Id)  //hard coded.
which leads to the same variables
count( {$<Date_LastActivityAt ={">=$(vGetFirstPrevMonth) <$(vGetLastPrevMonth))"}>} Id)

[Sumit2504]

@chris_djih  - Yes, Date_LastActivityAt is a date field with DD/MM/YYYY.

Can you help me with expression which can be used to create vGetFirstPrevMonth and vGetLastPrevMonth for Current selections.

[chris_djih]

i already have mentioned everything in the first post.
of Cause you have to replace "Date.Month" with the field name of your data model, which containts the information of the month.