topic Re: YTD does not work on chart in QlikView

YTD does not work on chart

Mon, 27 Apr 2015 14:49:19 GMT

I have an excel file with sales for each single article. This file contains also month and year of transaction. It was very easy to put everything on a straight table where I can select which year, month and family of article I want to analyse.

Then I added a column to visualize YTD data by the following expression:

=rangesum(above(sum(vSold), 0 , 12))

which works perfectly. I can also select less then 12 months and I still have the right YTD. (Note: at this stage it works only if the selected months are consecutive. But it is not a big issue)

Anyhow, when I tried to turn this table into a bar chart, the YTD value does not work even if I used same dimensions and same expressions.

Is there any way I can have the YTD on chart?

very important: YTD has to be separate for each family of article as it is on the straight table.

Thanks

Max

Re: YTD does not work on chart

Gysbert_Wassenaar — Mon, 27 Apr 2015 14:50:30 GMT

See this document: Calculating rolling n-period totals, averages or other aggregations

Re: YTD does not work on chart

awhitfield — Mon, 27 Apr 2015 14:51:41 GMT

Hi Max,

can you post your QVW please? And the xls if possible?

Cheers

Andy

Re: YTD does not work on chart

Mon, 27 Apr 2015 15:27:15 GMT

Thanks Gysbert,

I already found this document. The problem is my months are named as text (i.e. GEN; FEB; MAR;....) so when I tried to use the script (replacing Month with Mese and SalesData with my table name), it results a null field.

AsOfMonth:
load
Month as Month_AsOf,
Month + 1 - IterNo() as Month
Resident SalesData
while IterNo() <= 3;

right join load Month Resident SalesData;

I was thinking that the problem is that in here months have to be numbers and not name.

Furthermore I am looking at YTD not rolling period. I suppose the example is into the file you attached but I am using a free version of QV so I cannot read any other file.

Re: YTD does not work on chart

Mon, 27 Apr 2015 15:28:44 GMT

Sorry Andrew but these data are sensible and I cannot post them.

Anyhow thanks.

Max

Re: YTD does not work on chart

ramoncova06 — Mon, 27 Apr 2015 16:01:32 GMT

use num(Month) to get the corresponding number for each month

Re: YTD does not work on chart

Gysbert_Wassenaar — Mon, 27 Apr 2015 16:56:32 GMT

Yes, that bit of code you posted needs numbers. But you can create the table in other ways. For example in excel and load it from the excel file.

I'm not sure you understand the AsOf table concept. Perhaps after reading that part of the document again you'll understand better how it works. Then you should have no problem applying the technique to create a YTD AsOf table.