topic Re: YTD representation by month in QlikView

YTD representation by month

Thu, 09 Jun 2016 15:58:20 GMT

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

Re: YTD representation by month

sunny_talwar — Thu, 09 Jun 2016 16:11:31 GMT

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

Re: YTD representation by month

Thu, 09 Jun 2016 16:24:49 GMT

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

Re: YTD representation by month

sunny_talwar — Thu, 09 Jun 2016 16:40:54 GMT

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

Re: YTD representation by month

Thu, 09 Jun 2016 16:47:44 GMT

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

I got it now. Thanks a lot!

Re: YTD representation by month

psk180590 — Fri, 23 Jun 2017 11:27:50 GMT

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.

Re: YTD representation by month

sunny_talwar — Fri, 23 Jun 2017 11:29:09 GMT

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

Re: YTD representation by month

psk180590 — Fri, 23 Jun 2017 11:38:50 GMT

!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!!

Re: YTD representation by month

sunny_talwar — Fri, 23 Jun 2017 14:52:46 GMT

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?

Re: YTD representation by month

vishsaggi — Fri, 23 Jun 2017 15:00:33 GMT

Hello Sai,

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

Re: YTD representation by month

psk180590 — Mon, 26 Jun 2017 07:33:01 GMT

I would like to restrict people from selecting them in one chart and allow selection from the list box i have in the page in another chart.

Basically, if you could help me writing the below formula using a range sum it should satisfy my need.

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

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

Thanks.

Re: YTD representation by month

psk180590 — Mon, 26 Jun 2017 07:34:42 GMT

Hello Vishwarath,

Thanks for the suggestion makes sense.

I already have a separate discussion for this. I will move/link this to that once i find a proper solution.

Regards

Sai.

Re: YTD representation by month

psk180590 — Mon, 26 Jun 2017 13:34:59 GMT

Hello stalwar1‌

Just to be more clear, i want to pass multiple dimensions in the RangeSum expresson.

Re: YTD representation by month

psk180590 — Mon, 26 Jun 2017 13:41:14 GMT

I tried with this formula, but didn't yield any result.

=Aggr(RangeSum(Above(TOTAL Sum(Sales), 0, RowNo())),Market=('A'), Region=, Area=,Product =('ZZZ'),Year=('2017'))

Aggr(RangeSum(Above(TOTAL Sum(Sales), 0, RowNo())),Market=('A'), Region=, Area=,Product =('ZZZ'),Year=('2016'))

Re: YTD representation by month

sunny_talwar — Mon, 26 Jun 2017 13:45:22 GMT

Would you be able to update this in your attached sample to show where you are trying this? Although I know it isn't working, but it will help me see what your dimensions are. What might further help is if you can tell me the numbers you expect to see within your chart

Re: YTD representation by month

psk180590 — Mon, 26 Jun 2017 14:01:43 GMT

Attached my updated app. Also, included a few notes to clarify the issue.

Re: YTD representation by month

sunny_talwar — Mon, 26 Jun 2017 14:08:23 GMT

See if this looks good

Re: YTD representation by month

psk180590 — Mon, 26 Jun 2017 14:33:19 GMT

Perfect Man. Works exactly the way i wanted.

Re: YTD representation by month

sunny_talwar — Mon, 26 Jun 2017 14:33:54 GMT

Awesome

Re: YTD representation by month

psk180590 — Mon, 26 Jun 2017 14:34:22 GMT

Thank You Very Much!!