topic Re: Calculate the delta between two end dates in QlikView

Calculate the delta between two end dates

marakud971 — Mon, 26 Jan 2026 16:26:21 GMT

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

Re: Calculate the delta between two end dates

Taoufiq_Zarra — Fri, 12 Jun 2020 18:41:23 GMT

@marakud971

can you share a sample data and the expected output

Re: Calculate the delta between two end dates

marakud971 — Mon, 15 Jun 2020 05:35:43 GMT

Hi @Taoufiq_Zarra .

Here is an example of data.

There are dates informations, an other dimension CITY, and a measure NB.

I would like to make a line chart visualization which calculate delta between Year N and Year N-1 for all date element (delta = Measure N - Measure N-1)
I have to do calculation with last dat eof each periode.

In the picture, there is a filter (green) named "Dimensions".
I have to be able to display my measure by fo example CITY (or another dim).

Thanks a lot

Best Regards,

Harry

Re: Calculate the delta between two end dates

Taoufiq_Zarra — Mon, 15 Jun 2020 10:57:04 GMT

Bonjour @marakud971

You can add for example {$<Year={"$(=Max(Year))"}>} for Year and {$<Year={"$(=Max(Year)-1)"}>} for YEAR-1

I can't suggest the exact script since I haven't understood what you want to do yet.

Can you give more detail or ana example.

Merci pour ton retour

Re: Calculate the delta between two end dates

marakud971 — Mon, 15 Jun 2020 12:56:44 GMT

Hi @Taoufiq_Zarra ,

sorry, i will give you more explanations.

For example :

if in my visualization, i've got a hierachy Year-> Semester->Quarter-Month->Week->Day

if i'm in the year level, i would like do to : max(2020) - max(2029)
if i'm in the semester level, i would like do to max(Semester 1 of 2020) - max(Semester 1 of 2019) and max(Semester 2 of 2020) - max(Semester 2 of 2019). In my line chart semesters are on axis X.
if i'm in the quarter level, i would like do to max(quarter 1 of 2020) - max(quarter 1 of 2019) and max(quarter 2 of 2020) - max(quarter 2 of 2019) and max(quarter 3 of 2020) - max(quarter 3 of 2019)....etc...
About Week it's the same logic.

I have to find the last date of each period when i calculate the difference.

In a second part,

i have to aggregate this measure if i would like to display a dim values in my chart.

Is my need clearer ?

Thanks @Taoufiq_Zarra

Re: Calculate the delta between two end dates

Taoufiq_Zarra — Mon, 15 Jun 2020 14:43:54 GMT

@marakud971

If I understand correctly, you always compare the max year (or the selected year) with the year -1.
so I think you just need :

=Max({$<Année={"$(=Max(Année))"}>} CONTRACT_COUNT)-Max({$<Année={"$(=Max(Année)-1)"}>} CONTRACT_COUNT)

in the other levels if you select, month, semester, ..., the same formula will work, since it will compare the selected month, semester,... of the year with year-1.

attached qvw file

Don't hesitate if you have any other remarks.

Re: Calculate the delta between two end dates

marakud971 — Mon, 15 Jun 2020 16:14:01 GMT

thanks @Taoufiq_Zarra .

I tried your solution. It works well in a textbox. Thanks !

But how to adapt this measure in a line chart ?
When i try, my visualisazion stay blank.

Thanks again.

Re: Calculate the delta between two end dates

Taoufiq_Zarra — Mon, 15 Jun 2020 16:23:13 GMT

@marakud971 how you conceived the line chart, what do you expect for the x-axis and y?

Re: Calculate the delta between two end dates

marakud971 — Mon, 15 Jun 2020 17:06:44 GMT

@Taoufiq_Zarra

x-axis : my date hierarchy
y-axis : values
Legend : colunm CITY

I updated my data file.
you'll find in attachment
You'll also find a picture in this xls file.

Regards,
Harry

Re: Calculate the delta between two end dates

Taoufiq_Zarra — Mon, 15 Jun 2020 19:07:50 GMT

@marakud971 I'm sorry, but I still can't fully understand the need ?
are the values is Max year-(Max year-1)?
can you give me a simple example to understand:

I want to display the XXXX values for T1 for MArtinique as an example?

attached the version

Re: Calculate the delta between two end dates

marakud971 — Mon, 15 Jun 2020 19:57:11 GMT

@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

Re: Calculate the delta between two end dates

Taoufiq_Zarra — Mon, 15 Jun 2020 22:02:15 GMT

@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

Re: Calculate the delta between two end dates

marakud971 — Tue, 16 Jun 2020 16:40:57 GMT

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,

Re: Calculate the delta between two end dates

Taoufiq_Zarra — Wed, 17 Jun 2020 09:48:34 GMT

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

Re: Calculate the delta between two end dates

marakud971 — Wed, 17 Jun 2020 12:32:07 GMT

Hi !

Sorry !

You'll find the file in attachment.

Best,

Re: Calculate the delta between two end dates

marakud971 — Thu, 18 Jun 2020 13:19:33 GMT

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 ?

Re: Calculate the delta between two end dates

Taoufiq_Zarra — Thu, 18 Jun 2020 21:59:56 GMT

can we try something on load script ?

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

Re: Calculate the delta between two end dates

marakud971 — Fri, 19 Jun 2020 04:45:54 GMT

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

Re: Calculate the delta between two end dates

Taoufiq_Zarra — Fri, 19 Jun 2020 16:36:08 GMT

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.

Re: Calculate the delta between two end dates

marakud971 — Fri, 19 Jun 2020 17:47:17 GMT

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.