Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, good day to all of you.
I'm trying to do a KPI in which I need to have 2 variables, the first one with the sum of the "Past due portfolio" field for the selected period and the other one having the accumulative sum of "sales" field for the the full year before the selected period.
How can I do the second one, I'm using a filter that restricts that users can only select one period.
Thanks a lot, I know it seems easy but I'm kinda new to Qlik Sense.
Cordially,
Diego Vélez
Qlik sense student.
Hi Bala Thanks a lot for your answer,
I learnt a lot reading the topic you shared with me, and tried to use your formula and some of the formulas in the other post, they worked somehow but were also giving some trouble to me when I was trying to show the data as I wanted to, also my period format "MMYYYY" and the fact that I needed to sum the full 12 months rolling, including this year and the previous one were also problematic at some moment.
So I ended up doing a formula that included all of the periods one by one with some kind of variations.It's not practical but did the trick.
Sum({<PERIODO={$(=Max(PERIODO))}>} [VALOR_CONSUMO_ACTIVA])
sums the selected period
+
if
(
mid(Max({$} [PERIODO]),5,2) -1 <= 0,
Finds out if the previous PERIOD to the selected one is in this year (ex for 201801 is 01 and the previous PERIOD would be 201712)
Sum({<PERIODO={$(=(Max(PERIODO)-89))}>} [VALOR_CONSUMO_ACTIVA]),
if the previous PERIOD to the selected one is in the past year adds the value for this PERIOD , this situation only happens when the selected period is YYYY01 then the difference will always be 89 and will grow by 1 with each period on the formula.
Sum({<PERIODO={$(=(Max(PERIODO)-1))}>} [VALOR_CONSUMO_ACTIVA])
if the previous PERIOD to the selected one is in this year then just rests 1 to the selected PERIOD.
)
and it keeps going until the 12 PERIODS.
+
if
(
mid(
Max({$} [PERIODO]),5,2) -2 <=0,
Sum({<PERIODO={$(=(Max(PERIODO)-90))}>} [VALOR_CONSUMO_ACTIVA]),
Sum({<PERIODO={$(=(Max(PERIODO)-2))}>} [VALOR_CONSUMO_ACTIVA])
)
+
if
(
mid(
Max({$} [PERIODO]),5,2) -3 <=0,
Sum({<PERIODO={$(=(Max(PERIODO)-91))}>} [VALOR_CONSUMO_ACTIVA]),
Sum({<PERIODO={$(=(Max(PERIODO)-3))}>} [VALOR_CONSUMO_ACTIVA])
)
I believe the formula could be shorter using a growing variable but I ain't familiar with that yet.
Thanks a lot again.
May be this:
aggr(RangeSum(Above(Sum({<Date = {">=$(=Max(Date) - 11)<=$(=Max(Date))"}>} Units),0,12)), $(Var_Selected),Date)
Check this link:
Calculating rolling n-period totals, averages or other aggregations
Hi Bala Thanks a lot for your answer,
I learnt a lot reading the topic you shared with me, and tried to use your formula and some of the formulas in the other post, they worked somehow but were also giving some trouble to me when I was trying to show the data as I wanted to, also my period format "MMYYYY" and the fact that I needed to sum the full 12 months rolling, including this year and the previous one were also problematic at some moment.
So I ended up doing a formula that included all of the periods one by one with some kind of variations.It's not practical but did the trick.
Sum({<PERIODO={$(=Max(PERIODO))}>} [VALOR_CONSUMO_ACTIVA])
sums the selected period
+
if
(
mid(Max({$} [PERIODO]),5,2) -1 <= 0,
Finds out if the previous PERIOD to the selected one is in this year (ex for 201801 is 01 and the previous PERIOD would be 201712)
Sum({<PERIODO={$(=(Max(PERIODO)-89))}>} [VALOR_CONSUMO_ACTIVA]),
if the previous PERIOD to the selected one is in the past year adds the value for this PERIOD , this situation only happens when the selected period is YYYY01 then the difference will always be 89 and will grow by 1 with each period on the formula.
Sum({<PERIODO={$(=(Max(PERIODO)-1))}>} [VALOR_CONSUMO_ACTIVA])
if the previous PERIOD to the selected one is in this year then just rests 1 to the selected PERIOD.
)
and it keeps going until the 12 PERIODS.
+
if
(
mid(
Max({$} [PERIODO]),5,2) -2 <=0,
Sum({<PERIODO={$(=(Max(PERIODO)-90))}>} [VALOR_CONSUMO_ACTIVA]),
Sum({<PERIODO={$(=(Max(PERIODO)-2))}>} [VALOR_CONSUMO_ACTIVA])
)
+
if
(
mid(
Max({$} [PERIODO]),5,2) -3 <=0,
Sum({<PERIODO={$(=(Max(PERIODO)-91))}>} [VALOR_CONSUMO_ACTIVA]),
Sum({<PERIODO={$(=(Max(PERIODO)-3))}>} [VALOR_CONSUMO_ACTIVA])
)
I believe the formula could be shorter using a growing variable but I ain't familiar with that yet.
Thanks a lot again.
Perhaps this?
Sum(Aggr(If(DateField>=Date(AddMonths(DateField,-11)) and DateField<=Date(Max(DateField)),Sum({<PERIODO={$(=Max(PERIODO))}>} [VALOR_CONSUMO_ACTIVA])
+
if
(
mid(Max({$} [PERIODO]),5,2) -1 <= 0,
Sum({<PERIODO={$(=(Max(PERIODO)-89))}>} [VALOR_CONSUMO_ACTIVA]),
Sum({<PERIODO={$(=(Max(PERIODO)-1))}>} [VALOR_CONSUMO_ACTIVA])
)
+
if
(
mid(
Max({$} [PERIODO]),5,2) -2 <=0,
Sum({<PERIODO={$(=(Max(PERIODO)-90))}>} [VALOR_CONSUMO_ACTIVA]),
Sum({<PERIODO={$(=(Max(PERIODO)-2))}>} [VALOR_CONSUMO_ACTIVA])
)
+
if
(
mid(
Max({$} [PERIODO]),5,2) -3 <=0,
Sum({<PERIODO={$(=(Max(PERIODO)-91))}>} [VALOR_CONSUMO_ACTIVA]),
Sum({<PERIODO={$(=(Max(PERIODO)-3))}>} [VALOR_CONSUMO_ACTIVA])
)),Dimension1,Dimension2))
Hi Anil, sorry for not answering before I was in a little trip, I don't really understand the last answer you gave, I was thinking in something like
Sum({<PERIODO={$(=Max(PERIODO))}>} [VALOR_CONSUMO_ACTIVA])
+
For i = 1 to 11
if
(
mid(
Max({$} [PERIODO]),5,2) -i <=0,
Sum({<PERIODO={$(=(Max(PERIODO)-(88+i)))}>} [VALOR_CONSUMO_ACTIVA]),
Sum({<PERIODO={$(=(Max(PERIODO)-i))}>} [VALOR_CONSUMO_ACTIVA])
)
Maybe you can help me to define that variable for that part of the formula so I can give you the correct answer, because you helped me a lot. Thanks again.
Thank you, i'm glad for giving you at least a small hint, to solve your issue.