Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Month selection and cumulative sum

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.

1.JPG                    2.JPG

1 Solution

Accepted Solutions

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


Capture.PNG

View solution in original post

5 Replies

You have default option cumulative from expression tab

And try

Sum(total amount)

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)

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)

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


Capture.PNG

View solution in original post

Hi,

maybe helpful:

The As-Of Table

regards

Marco

Contributor III
Contributor III

Thank you. It works.