topic Re: Display the 2 KPI for the Current Date and the Comparable Date in App Development

Display the 2 KPI for the Current Date and the Comparable Date

zjalalacssi — Sat, 16 Nov 2024 05:03:07 GMT

Hi Everyone,

I have an issue displaying Two Kpis with different Dates on the same Row.

I have a calendar with these values:

This calendar is linked to my fact table using the Date field

i need to display a table with the following informations:

Date	Kpis	Kpis Comparable Date
2018-01-06	40	10 (2017-01-07)

Using Set Analysis i have 2 rows Displayed like this:

Date	Kpis	Kpis Comparable Date
2018-01-06	40	0
2017-01-07	0	10

Can you help me figure it out please?

Thank you

Re: Display the 2 KPI for the Current Date and the Comparable Date

miskinmaz — Wed, 07 Aug 2019 10:12:45 GMT

what is the expression you have used in second expression

Kpis Comparable Date

Re: Display the 2 KPI for the Current Date and the Comparable Date

zjalalacssi — Wed, 07 Aug 2019 10:18:10 GMT

The First one: sum({<[Date]={"$(v_MaxDate)"}>} KPI)

The second one: sum({<[Date]={"$(v_MaxDateComp)"}>} KPI)

My Variables :

v_MaxDate=max(Date)

v_MaxDateComp=max(day_id_day_comp)

Thank you

Re: Display the 2 KPI for the Current Date and the Comparable Date

miskinmaz — Wed, 07 Aug 2019 10:31:56 GMT

Try to use two expression:

1 KPI: sum({<Date={"$(=max(Date))"}>}KPI)

2 KPI for prev day kpi: sum({<Date={"$(=max(Date)-1)"}><date={"$(=max(date))"}>}KPI)</date={"$(=max(date))"}>

Re: Display the 2 KPI for the Current Date and the Comparable Date

zjalalacssi — Wed, 07 Aug 2019 10:39:28 GMT

Thank you,

It's not the previous Day, it's a comparable day of last year.

Example: for: 06/07/2019 the comparable day is: 07/07/2018

Re: Display the 2 KPI for the Current Date and the Comparable Date

miskinmaz — Wed, 07 Aug 2019 10:57:21 GMT

ok so try

sum({<Date={"$(=date(addmonths(max(Date)-1,-12),'MM/DD/YYYY'))"}>}KPI)

Re: Display the 2 KPI for the Current Date and the Comparable Date

zjalalacssi — Wed, 07 Aug 2019 11:05:39 GMT

The hard part is that the Comparable day is already calculated by the company it's not a standard calculation rule, but it's calculated based on some other rules defined by the company.

for each date there is a comparable day , i can't apply addmonths(max(Date)-1,-12) for 2019-08-06 because the comparable day won't be equal to 2018-08-07

Re: Display the 2 KPI for the Current Date and the Comparable Date

miskinmaz — Wed, 07 Aug 2019 11:39:09 GMT

can you post the sample app. There are many factors that your expression is not working as expected

Re: Display the 2 KPI for the Current Date and the Comparable Date

zjalalacssi — Wed, 07 Aug 2019 12:13:57 GMT

Yes, here is a sample app of my data

the result i'm trying to have is:

Date	Sum(Nbcust)	Sum(Nbcust) Comparable Date
2019-08-06	14	15
2019-08-05	10	34
2019-08-04	65	44
2019-08-03	87	21
2019-08-02	46
2019-08-01	18
2018-08-07	15
2018-08-06	34
2018-08-05	44
2018-08-04	28
2018-08-03	21

Re: Display the 2 KPI for the Current Date and the Comparable Date

miskinmaz — Thu, 08 Aug 2019 05:08:06 GMT

this is not possible with the set analysis and the workaround will be through script.

Please find the attached file for the same. You need to join the comparable date again with the date and calculate the total sum where max date will be equal to date.

Re: Display the 2 KPI for the Current Date and the Comparable Date

zjalalacssi — Thu, 08 Aug 2019 07:52:20 GMT

Thank you very much for taking the time to help me, i will test your solution on my app (since i have a more complex modele) and let you know soon.