topic Re: Month selection and cumulative sum in QlikView

Month selection and cumulative sum

fatihtomruk — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have a listbox including months and a straight table including months and amount as below. I want to choise a month from listbox and I want to see cumulative sum and cumulative months in the table. Could you please help me?

thank you in advance.

Re: Month selection and cumulative sum

Anil_Babu_Samineni — Tue, 27 Sep 2016 17:53:06 GMT

You have default option cumulative from expression tab

And try

Sum(total amount)

Re: Month selection and cumulative sum

sunny_talwar — Tue, 27 Sep 2016 17:59:31 GMT

Do you have a date field? I would use a date field to do this:

Sum({<DateField = {"$(='>=' & Date(YearStart(Max(DateField)), 'DateFieldFormatHere') & '<=' & Date(Max(DateField), 'DateFieldFormatHere'))"}, MonthField>} Amount)

I made an assumption here that you will be looking at only one year of data, is that assumption true? If not, then may be this:

Sum({<DateField = {"$(='>=' & Date(SetDateYear(YearStart(Max(DateField)), Year(Max(DateField))), 'DateFieldFormatHere') & '<=' & Date(SetDateYear(Max(DateField), Year(Max(DateField))), 'DateFieldFormatHere'))"}, MonthField>} Amount)

Re: Month selection and cumulative sum

sunny_talwar — Tue, 27 Sep 2016 18:58:24 GMT

For all years, try this:

Sum({<MonthNum = {">=1<=$(=Max(MonthNum))"}, Month>} Amount)

Where MonthNum is created in the script using this code

Num(Month(Date)) as MonthNum

Attaching a sample for you

Table:

LOAD *,

Month(Date) as Month,

MonthName(Date) as MonthYear,

Year(Date) as Year,

Num(Month(Date)) as MonthNum;

LOAD Date(MakeDate(1999, 12, 31) + IterNo()) as Date,

Ceil(Rand() * 1000) * 100 as Amount

AutoGenerate 1

While IterNo() <= Today() - MakeDate(1999, 12, 31);

Re: Month selection and cumulative sum

MarcoWedel — Tue, 27 Sep 2016 21:33:03 GMT

Hi,

maybe helpful:

The As-Of Table

regards

Marco

Re: Month selection and cumulative sum

fatihtomruk — Wed, 28 Sep 2016 04:44:21 GMT

Thank you. It works.