topic Re: YTD calculation issue in QlikView

YTD calculation issue

Tue, 29 Mar 2016 02:24:46 GMT

I am trying to calculate (YTD) from the monthly data. Attached please find the sample Excel and .qvw.

I have State, Product, Tear and Month as dimensions and Sales as a measure. I am trying to calculate YTD for each Product.

Expression is Sum(Sales).

In the .PNG file as you can see If I select Mar, the pivot table below does not give me Product A, because Product 'A' does not have any sales in Mar. But I still want to see the cumulative for Product 'A' from Jan, Feb in Mar

Please let me know how I can do that. I have already tried the <= Month syntax and it does not work.

Re: YTD calculation issue

tresB — Tue, 29 Mar 2016 05:03:44 GMT

May be like attached sample.

PFA

Re: YTD calculation issue

amayuresh — Tue, 29 Mar 2016 10:52:08 GMT

Do you want output like below,

Expression: sum({<Month>}TOTAL <Product> Sales)

Re: YTD calculation issue

Tue, 29 Mar 2016 12:38:04 GMT

NO I want the aggregation by State, by Product

Re: YTD calculation issue

Tue, 29 Mar 2016 12:45:49 GMT

Its close . BUt when I select Product 'A' Mar becomes inactive. I would like to see March available to select if I select Product 'A'

Re: YTD calculation issue

tresB — Tue, 29 Mar 2016 12:52:10 GMT

Put some data for 'A' in March, you would be able to select March then.

Re: YTD calculation issue

Tue, 29 Mar 2016 13:05:31 GMT

Hi Rehan Saeed, in your pivot table I take out the dimension 'Month' because if you put this dimension, Qlikview always shows you the product by month, by product and by state.

In your example in Excel, you have not two sales for one product in some month and state and year, so Qlikview will never do the 'agrupation' because has not something to agrupate.

Regards,

Agustín !

Re: YTD calculation issue

Tue, 29 Mar 2016 13:08:34 GMT

BUt this is a actual problem. I want March to be available even If I select Product, with no data in Mar. So if it is a true YTD, then Mar will have the sum of (Jan and Feb). Looks like I need some sort of a master calendar to drive the Year Month fields from

I tried but no solution yet yet