Yearly cumulative per month

[americanetsMD]

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. 🙂

[Anil_Babu_Samineni]

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

[americanetsMD]

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%

...

[Anil_Babu_Samineni]

@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%

[americanetsMD]

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

[Anil_Babu_Samineni]

@americanetsMD Perhaps this?

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

[americanetsMD]

@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. 

[Anil_Babu_Samineni]

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

[americanetsMD]

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? 

[americanetsMD]

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