Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

calculate past 4 months total income

Hi,

Would appreciate some advice here.

I am trying to calculate the past 4 monts total income. I have a field period that is the yearmonth like 201102. I have tried the following but it is not working.

=

=Sum({$<Period={$(=Max(Period)-1)}>} (AmountExcl)) + Sum({$<Period={$(=Max(Period)-2)}>} (AmountExcl)) + Sum({$<Period={$(=Max(Period)-3)}>} (AmountExcl)) + Sum({$<Period={$(=Max(Period)-4)}>} (AmountExcl))



But what happen is it start correctly with the first yearmonth that is 201102 the with yearmonth 201101 then with yearmonth 201100 that is wrong - it is supposed to be 201012









and then with last month that is 201011 that is november

I have a datefield like 2011/01/01if i need to use a date field

Thank you

Louw



2 Replies
MVP
MVP

calculate past 4 months total income

Hello Louw,

You can use Date() function and AddMonths() to get the results you want for date values

=Date(AddMonths('2011/01/01', -1), 'YYYYMM')


Then use a variable instead of selecting in a field to let the user select the offset (if any) or do it manually in your formula

Sum({< Period = {'>=$(=Date(AddMonths(Max(Date#(Period, 'YYYYMM')), -4), 'YYYYMM'))'} >} AmountExcl)


Hope that helps.

Not applicable

calculate past 4 months total income

Thanks for your help Miguel it did work.

Appreciated

Louw

Community Browser