topic Re: YTD Analysis in QlikView

YTD Analysis

techvarun — Tue, 27 Aug 2013 14:00:02 GMT

Hi,

I have data for two years and i want to display the result in a particular format. I have all the formulas and values are ready with me and needs the help from you to display the data in the format as per requirement.

attached the file which I have and the format which I need to show the output.

Attached is the Screenshots of the Input and output values

Thanks in Advance

Re: YTD Analysis

CELAMBARASAN — Tue, 27 Aug 2013 14:03:59 GMT

Can you attach sample file?

Re: YTD Analysis

techvarun — Tue, 27 Aug 2013 14:46:14 GMT

Hi Celamb,

Thanks for ur quick reply.

Updated the question with input excel.

Regards

Re: YTD Analysis

jpapador — Tue, 27 Aug 2013 14:49:32 GMT

You can create a Year to Date field in your calendar that will flag dates in previous years up to today. It would look something like this in your script:

If(InYearToDate(Date, $(vTodaysDate), 0)

or InYearToDate(Date, $(vTodaysDate), -1)

or InYearToDate(Date, $(vTodaysDate), -2)

or InYearToDate(Date, $(vTodaysDate), -3)

or InYearToDate(Date, $(vTodaysDate), -4)

, 'YTD') as YTD,

Re: YTD Analysis

Gysbert_Wassenaar — Tue, 27 Aug 2013 17:21:08 GMT

See attached qvw.

Re: YTD Analysis

techvarun — Wed, 28 Aug 2013 07:34:07 GMT

Am using QlikView P E

Re: YTD Analysis

Gysbert_Wassenaar — Wed, 28 Aug 2013 16:30:35 GMT

This is the script:

CrossTable(Year,Amount,2)

LOAD subfield(Year,'_',1) as KPI, SubField(Year,'_',-1) as Period,

[2013],

[2012]

FROM Input_YTD.xls (biff, embedded labels, table is [Sheet1$]);

And the pivot table looks like this:

You can see the only expression of the pivot table in the caption. The dimensions Period and Year have been dragged to above the expression so they show horizontally.