Month selection and cumulative sum

[fatihtomruk]

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.

                   

[Anil_Babu_Samineni]

You have default option cumulative from expression tab

And try

Sum(total amount)

[sunny_talwar]

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)

[sunny_talwar · Accepted solution]

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

[MarcoWedel]

Hi,

maybe helpful:

The As-Of Table

regards

Marco

[fatihtomruk]

Thank you. It works.