Calculate the delta between two end dates

[marakud971]

 Hi everyone !

I am contacting you to request your assistance.

I would like to create a line chart
which calculates delta between  N and N-1 (years, semester, quarter, month, days).
I made an hierarchy for that.

The blocking point is that the calculation must always select the last date of the selected period.

For example:

If I'm on a view per month,
I would calculate 31/01/2010 - 31/01/2019 and the same for all months.

Thanks a lot

[Taoufiq_Zarra]

That's what I found 2020-2019 for Guadeloupe? Is that normal?

[marakud971]

In attachment you'll find the the result of the data in excel.

We need to sum all data of the last date of selected period.

[Taoufiq_Zarra]

@marakud971  I still can't figure out what's missing.

=sum(CONTRACT_COUNT) is your measure ?

[marakud971]

Yes ! it's sum(CONTRACT_COUNT).

We need to calcule sum(CONTRACT_COUNT) for previous year (2019) + S1 + max(date of S1 of previous year)

In the formula , i think max(date of S1 of previous year) is wrong.

[Taoufiq_Zarra · Accepted solution]

@marakud971 

with the attached version, I think we have the same values.

The alternative is to calculate it in script and then apply it in measure

S:

=if(isnull(Max({$<YEAR={$(=Max(YEAR))}>} S_CONTRACT_COUNT)),0,Max({$<YEAR={$(=Max(YEAR))}>} S_CONTRACT_COUNT))
-if(isnull(Max({$<YEAR={$(=Max(YEAR)-1)}>} S_CONTRACT_COUNT)),0,Max({$<YEAR={$(=Max(YEAR)-1)}>} S_CONTRACT_COUNT))

T:

=if(isnull(Max({$<YEAR={$(=Max(YEAR))}>} T_CONTRACT_COUNT)),0,Max({$<YEAR={$(=Max(YEAR))}>} T_CONTRACT_COUNT))
-if(isnull(Max({$<YEAR={$(=Max(YEAR)-1)}>} T_CONTRACT_COUNT)),0,Max({$<YEAR={$(=Max(YEAR)-1)}>} T_CONTRACT_COUNT))

[marakud971]

Hi @Taoufiq_Zarra  !!!

Thanks a lot ! 

I tried your code for SEMESTER, QUARTER and MONTH ! it's wonderful !

So i have to create a tmp block for each elements i would like display.

For instance  : 

- Semester and City
- Semester and dim2
- Semester and dim3
- Quarter and City
- Quarter and dim2
....
...
- Month and City
...
..

If my dimensions are in the same table of my dates, everythings is okey.

But, if i would like to use a dim which is in an other table, do i have to use a new join table in my "group by" request ?

I want to thank you very much because  I was seriously stuck !