cumulative total for the current month

[Anonymous]

Hi all,

I need to show the current month current year cumulative total.

For example we are in month of may.

So total has to show all the sales till may.

the required output is this:

Month	CY Sales
May	6000

The data can be :

Month    Sales      Year

Jan         1000       2017

Feb         1000       2017

Mar          1000      2017

Apr           1000      2017

May           2000     2017

Apr               100     2016

May              200    2016

any help would be helpful

thanks

Anupama Jagan

[Anonymous]

One way (of many) would be

Add a flag in your script for current year.

load *,

if(YourYearField=year(today()),1,0) as CurrYear

resident YourTable;

Then your expression would be

=sum(TOTAL Sale*CurrYear)

Then put a Dimension Limit of Largest 1. (Uncheck Show Others)

[Anonymous]

Hi Wallo,

When I tried that im getting the correct sales count but the month is shown as dec and not may

Thanks,

Anupama Jagan

[Anonymous]

Try this.

Added a CurrMonth flag in the script.

Add it as a hidden dimension in the chart.  And put a dimension limit on that dimension only.

[Anonymous]

For some reason im still not getting it.

I tried all the steps you have done in your application.

Im getting all the months and the same cumulative value populated in it.

Something like this

Month   Sales

Jan          13700

Feb         13700

Mar          13700

Apr            13700

May           13700

Jun           13700

Jul            13700

Aug          13700

Sep         13700

Oct          13700

Nov          13700

Dec        13700     

[Digvijay_Singh]

You may do like this, since you want max month but don't want calculation in dimension, you can have expression for the max month, but then you need to hide your month dimension like this, you can hide dimension in the presentation tab -

[Digvijay_Singh · Accepted solution]

With changed labels -

[tyagishaila]

Once try Rangesum() function for cumulative Sale.

[tyagishaila]

Example-

TAB1:

LOAD * INLINE [

    Month, Sale

    1, 1000

    2, 1000

    3, 2000

    4, 1000

    5, 2000

];

TAB2:

NoConcatenate

Load *,RangeSum(Sale,Peek(SaleN)) As SaleN

Resident TAB1

Order by Month;

Drop Table TAB1;

exit Script;

Month	Sale	SaleN
1	1000	1000
2	1000	2000
3	2000	4000
4	1000	5000
5	2000	7000

[Anonymous]

It worked ! Thanks a lot Digvijay

[shiveshsingh]

Hi

please share the set analysis used in this problem