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

[marakud971]

@Taoufiq_Zarra , I drew it in attachment.

For the year view, i think i'll display the result in a textbox.

I drew examples  for semesters and quarters.

Tell me if it's understandable.

Thanks,

Regards,

Harry

[Taoufiq_Zarra]

@marakud971 

It must be understood that the use of Set analysis is not row by row, That's why I don't see how to simply implement your request. especially Max(...) can return nulls instead of 0 which will return error after.

I've attached a new file where I tried to fix this proble.

the idea is to create Dimension: Valuelist(...)

and in expression use ValueList to calculate Max year and MaxYear-1

[marakud971]

Hi ! @Taoufiq_Zarra 

Thanks for you help.

I tried your file and when i select in my year filter 2020 et GUADELOUPE

For Semester :

Delta S1 2020/2019 = S1/2020 - S1/2019 = 10/06/2020 - 30/06/2019 = 231185 - 241121 = -9936

When i look your result, i don't have this result for S1.

Furthermore,  i've got a new blocking point.
In my first attachment, i did not put exactly the data I have my database.

My measure CONTRAT_COUNT is calculated as below :
SUM(CONTRACT_COUNT)

You will understand why with this new attachment.
I put a new file with more similar structure .

So i tried to modify your formula like this :
=SUM({$<Année={"$(=Max(Année))"}, semestre={'S1'}, DATE_CODE={"$(=Max(DATE_CODE))"}>}  CONTRACT_COUNT)

But the formula does not work for 2019.

Any idea ?

Thanks a lot for your help !!!!

Best,

[Taoufiq_Zarra]

the sample data sent contains only  2019 so it's impossible for me to see the error no year-1 to compare with

[marakud971]

Hi ! 

Sorry !

You'll find the file in attachment.

Best,

[marakud971]

Hi @Taoufiq_Zarra 

In another post, someone had proposed this expression to calculate the SUM for end dates of one year.

: =FirstSortedValue( aggr(sum(value), Date), aggr( -Date, Date) )

Do you think it's possible to adapt this expression for previous year ?

[Taoufiq_Zarra]

can we try something on load script ?

I haven't watched today because the sample data sent contains always only 2019

[marakud971]

Hi ! Sorry again

There is a wrong conversion of *cvs to *xls.

So i send you he file thanks to WeTransfer : https://we.tl/t-Q8MT1z0966  .

 Yes, we can try everythings on load script  ! 

have you got an idea ? 

Best,

Harry

[Taoufiq_Zarra]

The file sent contains only the data of GUADELOUPE ?

what is the value we should normally obtain for S1 and S2?

 the formula sent with ValueList is an option that we can improve it.

[marakud971]

Yes ! There is too much data so i have limited them.

There is only guadeloupe.

I think that if if work for Guadeloupe, i'll bi oki for all dim CITY.

I try to improve your formula to find make a SUM of all contract, by what i do is wrong.

I don't find the good formula to do a sum périod and to the difference.