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

[zjalalacssi]

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

[miskinmaz]

what is the expression you have used in second expression 

Kpis Comparable Date

[zjalalacssi]

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

[miskinmaz]

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))"}>

[zjalalacssi]

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

[miskinmaz]

ok so try 

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

[zjalalacssi]

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

[miskinmaz]

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

[zjalalacssi]

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

[miskinmaz · Accepted solution]

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.