Period over same length period

[keliane]

Hi all,
Sorry for my imperfect English, I'm French.
Here is my problem, I want to compare two different time period so I can see the sum of the Net Income difference (and especially where is it coming from). 

I already achieve to do it with 2 different dates (basically comparing Net Income of 2024-02 with Net Income 2024-04) but I now want to do more. I would like to compare 2 periods of the same length (2024-02 to 2024-04 total net income compare to 2023-02 to 2023-04 total net income)

I think I've be precise enough so you an understand my problem. I'm already using auto calendar. 

My two date variables are named vSelectedMonth1 and vSelectedMonth2 and here is the script I use to show the sum of the net income for the chosen date : 

Sum({
<[Date.autoCalendar.Year]={$(=Year(Date#(vSelectedMonth1, 'YYYY-MM')))},
[Date.autoCalendar.Month]={$(=Date(Date#(vSelectedMonth1, 'YYYY-MM'), 'MMM'))}
>} [Net Income])

This is working perfectly. Would like to have two more variables vSelectedMonth3 and vSelectedMonth4 so 4 of them can create 2 periods of time.

Thank you for your future answer, and if you have further question, ask me ! 

PS : It seems like my YearMonth auto calendar is not working ? So I split it in two different lines Year and Month AND my variables are set as the format YYYY-MM but auto calendar is set as YYYY-MMM so that's why there is 'MMM' hiding somewhere.

Thank you !!

[keliane]

Update : I can up with this ? Which seems to calculate the sum of net income between two years ? But still full of bugs (only working in one direction) 

Sum({
<date = {">=$(=Date(Date#(vSelectedMonth1, 'YYYY-MM'))) <=$(=Date(Date#(vSelectedMonth2, 'YYYY-MM')))"}>
} [Net Income])

PS: I receive the net income data monthly.


I'm stuck on this please help me Qlik family 

[gomeri]

Hi @keliane, I hope I understood your request correctly.

To compare 2 periods of the same lenght you can creat and compare two different expressions, for ex. in a Line Chart.

First Expression: 

Sum({<[Date.autoCalendar.Year]={$(=Year(Date#(vSelectedMonth1, 'YYYY-MM')))},
[Date.autoCalendar.Month]={$(=Date(Date#(vSelectedMonth1, 'YYYY-MM'), 'MMM'))}
>} [Net Income])
-
Sum({<[Date.autoCalendar.Year]={$(=Year(Date#(vSelectedMonth2, 'YYYY-MM')))},
[Date.autoCalendar.Month]={$(=Date(Date#(vSelectedMonth2, 'YYYY-MM'), 'MMM'))}
>} [Net Income])

Second Expression: 

Sum({<[Date.autoCalendar.Year]={$(=Year(Date#(vSelectedMonth3, 'YYYY-MM')))},
[Date.autoCalendar.Month]={$(=Date(Date#(vSelectedMonth3, 'YYYY-MM'), 'MMM'))}
>} [Net Income])
-
Sum({<[Date.autoCalendar.Year]={$(=Year(Date#(vSelectedMonth4, 'YYYY-MM')))},
[Date.autoCalendar.Month]={$(=Date(Date#(vSelectedMonth4, 'YYYY-MM'), 'MMM'))}
>} [Net Income])

Let me know if I understand correctly and if this is the result you would like to achieve.

For problem with "YearMonth" i suggest you to see what is the type of this dimension? Can you attack some screenshot, please?

Thanks

[keliane]

Hi @gomeri, first of all : thank you for your time !

I've tried the expressions you sent me, and it seems to work well to calculate the difference between 2 different dates. The thing is that it's not what I'm looking for haha, but still thank you.

Let me try to explain it better : Each month, my company (bank) is doing a Situation Report that's including the net income for each month with there different category. The thing is, for the CEO, it's very hard to see WHERE the decrease or the increase is coming from. Maybe she will see that Corporate Finance has increase by 10%, but to see exactly which client is it it's a nightmare. I'm trying to make a tab so it can be more easy for her. She could travel inside the data to see where is it coming from AND (more important) compare 2 different period of time. I cannot screenshot any data because it's my company policy, but let me join you an excel that can help you illustrate what I want.

Thank for your time again.

And for the YearMonth field, I don't get what do you mean by type of dimension ? But I think it's manageable without this field.

If you have any other question, please ask me. 

[keliane]

Yes so to resume : I don't want to compare 2 different date, I want to compare 2 different period of time.

Example : sum net Income 01-2024 to 04-204 COMPARE TO sum net income 01-2023 to 04-2023

Basically : Sum Net Income 01-2024+02-2024+03-2024+04-24 COMPARE TO 01-2023+02-2023+03-2023+04-2023

Hope it's clearer haha 

[keliane · Accepted solution]

I (of course) solved my problem, so if someone ever have the same, I'm uploading the code so you could compare 2 differents period of time that are adjustable with varible input:

--> Calculation Of [Net Income] between date 1 (vSelectedMonth1) as YYYY-MM and date 2 (vSelectedMonth2) as YYYY-MM.

Sum({
<[Date.autoCalendar.Date]={"$(='>=' & Date(Date#(vSelectedMonth1, 'YYYY-MM')) & '<=' & Date(Date#(vSelectedMonth2, 'YYYY-MM')))"}>
} [Net Income])

Was not that hard, but I finally manage to go more and more complex.