YTD representation by month

[#1]

I would like to represent YTD sales by month.

Fiscal Year    Month      Sales YTD

2016             Jan           Jan (Sales)

2016             Feb           Jan+Feb ( Sales)

2016             March        Jan+Feb+March (Sales)

2015             Jan           Jan (Sales)

2015             Feb           Jan+Feb (Sales)

It is rolling months addition but it should not be static. I can have any number of months. I would like to show Current month's data from Jan month till current month for particular year.

I have tried accumulative sum but its not working as I have more than one dimension. Rangesum is not working as I have do not have specific rolling period, it could be anything depends on the current month.

Help me !

Mugdha

[sunny_talwar]

Look here for some resources on this topic:

Accumulative Sums

YTQ, QTD, MTD and WTD

QlikView App: Year To Date Using Set Analysis

The As-Of Table

For more specific help, please provide a sample

[#3]

I have checked all the posts but none of them are showing data by month.

Fiscal Year    Month      Sales YTD

2016             Jan           10

2016             Feb           20+10  =30

2016             Mar           20+30  =50

2016             Apr            60+50 = 110

2016             May          20+110  = 130

2015             Jan           30

2015             Feb           20+30 = 50

I tried ignoring month dimension from my expression but its not working as I have dimension Month in my table

[sunny_talwar · Accepted solution]

This is what you are looking for, right?

Sample Script:

Table:

LOAD Date,

  Ceil(Rand() * 100) * 100 as Sales,

  Month(Date) as Month,

  Num(Month(Date)) as MonthNum,

  Year(Date) as Year;

LOAD Date(YearStart(Today(), -1) + IterNo() - 1) as Date

AutoGenerate 1

While IterNo() <= 731;

Straight Table:

Dimensions:

Year

Month

Expression:

=RangeSum(Above(TOTAL Sum(Sales), 0, MonthNum))

[#5]

Yes, That's right! I was using =rangesum(above(sum(Sales),0,rowno()))

I got it now. Thanks a lot!

[psk180590]

Hello stalwar1,

I'm looking for a similar solution as this. But, the only change is i have a few dimensions to be Incorporated in the formula.

=Sum({<Market={'A'},Region=, Area=, Product = {'ZZZ'},Year ={'2017'}, Month=>}Sales)

/

Sum({<Market={'A'},Region=, Area=, Product = {'ZZZ'},Year ={'2016'},Month=>}Sales)

Can you please help me changing my formula to the one you mentioned above.

=RangeSum(Above(TOTAL Sum(Sales), 0, MonthNum))

Thanks.

Sai.

[sunny_talwar]

It might be easier if you are able to share a sample

[psk180590]

!I have attached an sample here.

I would like to have the Sales Current YTD per month vs Last Year as in your application. But, pass the region and area dynamically.

Also, this would be in a bar graph.

Thanks Much!!

[sunny_talwar]

But, pass the region and area dynamically.

When you say pass the region and area dynamically, what exactly do you mean? Make selections in those fields or use them as dimensions?

[vishsaggi]

Hello Sai,

Can you move this question creating a separate discussion, so that will be helpful to others. Just a suggestion.

V.