topic Yearly cumulative per month in App Development

Yearly cumulative per month

americanetsMD — Sat, 10 Feb 2024 16:49:43 GMT

Hello Community,

Could anyone please help me with an advice on how to calculate the cumulative sum based on the following formula:
Value3 = Value1(t0) - Value1(t-1)/ Value2

Having the following Test Data i should get the results:

AsOfPeriod Value3

12.2022 36,08%

01.2022 -233,91%

02.2022 132,40%

and so on..

i currently use An AsOfTable in order to calculate Value1 and Value2, as these should be also cumulative sumed.

My problem is that i don't know how can i jump to december, when i have to calculate the value for january.

Could anyone please help me with an idea? OH, yes... the Above() doesn't help much, as i need this calculated in a line chart.

Will be really grateful if anyone can helpl me with any information. 🙂

Re: Yearly cumulative per month

Anil_Babu_Samineni — Sun, 11 Feb 2024 05:29:35 GMT

@americanetsMD Please help us what is the input and expected result?

Re: Yearly cumulative per month

americanetsMD — Sun, 11 Feb 2024 11:45:05 GMT

Hi @Anil_Babu_Samineni , a data input sample is:

AsOfPeriode | Value1 | Value2 | Expected Value3
11.2021 | 8754728 | -22890516 | -

12.2021 | -4521628 | -39283282 | 36,08%,

01.2022 | -1257103 | -1395650 | -233,91%

02.2022 | -6346161 | -3843735 | 132,40%

The Formula, used to get Value 3:

Value3 = Value1(t0) - Value1(t-1)/ Value2
This would be so:
for AsOfPeriode 02.2022, Value3 = ((-6346161) - (-1257103)) / (-3843735) = -233,91%
for AsOfPeriode 01.2022, Value3 = ((-1257103) - (-1395650)) / (-1395650) = 132,40%

...

Re: Yearly cumulative per month

Anil_Babu_Samineni — Sun, 11 Feb 2024 19:11:24 GMT

@americanetsMD Thanks for the input, I wonder for the below one why -1395650 And not -4521628? Because t-1 should be 12.2021 right?

for AsOfPeriode 01.2022, Value3 = ((-1257103) - (-1395650)) / (-1395650) = 132,40%

Re: Yearly cumulative per month

americanetsMD — Sun, 11 Feb 2024 19:34:39 GMT

Hey @Anil_Babu_Samineni , yes, you are right...
for AsOfPeriode 01.2022, Value3 = ((-1257103) - (-4521628)) / (-1395650) = 132,40%

Re: Yearly cumulative per month

Anil_Babu_Samineni — Sun, 11 Feb 2024 19:45:08 GMT

@americanetsMD Perhaps this?

(Sum(Value1) - Above(TOTAL Sum(Value1)))/Sum(Value2)

Re: Yearly cumulative per month

americanetsMD — Sun, 11 Feb 2024 20:00:48 GMT

@Anil_Babu_Samineni It has worked... Thank you very much!
But when using above function without (TOTAL) it didn't work... could you explain me, maybe through a very basic example, what the TOTAL function does? Would really appreciate.

Re: Yearly cumulative per month

Anil_Babu_Samineni — Sun, 11 Feb 2024 20:03:32 GMT

@americanetsMD Glad we able to help, You can read this: Above - chart function | Qlik Sense on Windows Help

Re: Yearly cumulative per month

americanetsMD — Mon, 12 Feb 2024 09:20:17 GMT

Hi @Anil_Babu_Samineni ,
After using your suggested formula, I tested it to make sure everything worked as expected. Unfortunately it doesn't meet the requirements as nothing is displayed for the minimum month.
I mean, if the user selects a whole year, January will show up as zero... even though there is data from the previous year. Because of this, Above Function is not the solution for this task. 😞

Is there an alternative on how i can achieve the desired output?

Re: Yearly cumulative per month

americanetsMD — Mon, 12 Feb 2024 09:38:28 GMT

also when a single Period is selected, the chart gives a null... 😞

Re: Yearly cumulative per month

Anil_Babu_Samineni — Mon, 12 Feb 2024 11:06:50 GMT

@americanetsMD Can you please attach the QVF that you want to test and explain each one of your issues, so we will see!

Re: Yearly cumulative per month

americanetsMD — Wed, 14 Feb 2024 11:32:22 GMT

Hi @Anil_Babu_Samineni - Thank you for your support. I have found the way to get the right values by using the AsOfMonth in combination with YearDiff and MonthDiff...

sum(Value1) - sum({<MonatDiff -= {0}>} Value1)