Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
diego1991
Contributor III
Contributor III

Sum 12 months data according to the selected period

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.

1 Solution

Accepted Solutions
diego1991
Contributor III
Contributor III
Author

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.

View solution in original post

5 Replies
balabhaskarqlik

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

diego1991
Contributor III
Contributor III
Author

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.

Anil_Babu_Samineni

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))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
diego1991
Contributor III
Contributor III
Author

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.


balabhaskarqlik

Thank you, i'm glad for giving you at least a small hint, to solve your issue.