topic Re: Multiple date selection for KPI in App Development

Multiple date selection for KPI

Sumit2504 — Mon, 14 Feb 2022 05:44:31 GMT

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

Re: Multiple date selection for KPI

chris_djih — Mon, 14 Feb 2022 14:22:48 GMT

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.

Re: Multiple date selection for KPI

Sumit2504 — Mon, 14 Feb 2022 17:28:22 GMT

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?

Re: Multiple date selection for KPI

Sumit2504 — Tue, 15 Feb 2022 06:25:34 GMT

Hi @chris_djih ,

How to show the range for selected months ?

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

Re: Multiple date selection for KPI

chris_djih — Tue, 15 Feb 2022 07:35:38 GMT

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,'|').

Re: Multiple date selection for KPI

Sumit2504 — Tue, 15 Feb 2022 08:38:01 GMT

@chris_djih - Thank you for your help and support

Re: Multiple date selection for KPI

Sumit2504 — Tue, 15 Feb 2022 09:52:46 GMT

@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

Re: Multiple date selection for KPI

chris_djih — Tue, 15 Feb 2022 12:21:54 GMT

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)

Re: Multiple date selection for KPI

Sumit2504 — Tue, 15 Feb 2022 12:43:16 GMT

@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.

Re: Multiple date selection for KPI

chris_djih — Tue, 15 Feb 2022 12:51:33 GMT

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.