Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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. 🙂
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)
@americanetsMD Please help us what is the input and expected result?
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%
...
@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%
Hey @Anil_Babu_Samineni , yes, you are right...
for AsOfPeriode 01.2022, Value3 = ((-1257103) - (-4521628)) / (-1395650) = 132,40%
@americanetsMD Perhaps this?
(Sum(Value1) - Above(TOTAL Sum(Value1)))/Sum(Value2)
@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.
@americanetsMD Glad we able to help, You can read this: Above - chart function | Qlik Sense on Windows Help
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?
also when a single Period is selected, the chart gives a null... 😞