topic Re: YTD calculation in QlikView - Simple Example needed.. in QlikView

YTD calculation in QlikView - Simple Example needed..

Thu, 04 Jul 2013 07:46:59 GMT

Hello friends,

Just wondering if there is a simple step by step example covering how to do YTD calculations using set analysis.

I've set up a mock data set that kinda replicates my data structure in the attached xlsx.

This is a deliberately dumbed down example extracted from a far more complex DWH just so I can nut out how to do this.

Obviously I am trying to retreive base numbers from both years as well as a MTD number for each month.

Currently trying to do what should be a reasonably simple YTD calculation on a single measure column and finding this impossibly difficult!

Examples I've looked at - (amongst others)

a) http://community.qlik.com/message/363423

b) http://community.qlik.com/docs/DOC-4321

ATM I am trying the this formula from example b) above -

sum({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} MonthNumber))>$(=max({<Year={$(=max(Year))}>} MonthNumber)-1)"}>} Value)

which btw seems incredibly convoluted for something like this but then again being new to this possibly this is just 'culture shock'..

Also, is there an easy way to learn this - syntax seems incredibly difficult to understand for the more complex cases!

I must also add I am using the Free edition of QV Desktop for evaluation purposes.

Any help would be great!

(quick screen shot below of actual / desired results)

I am not posting the qvw since I wanted to figure out the best way to do this rather than a altered version of my (flawed) attempt!

Thanks

Re: YTD calculation in QlikView - Simple Example needed..

Thu, 04 Jul 2013 13:26:25 GMT

Nope, no dice still same result I am afraid..and formula is still quite complex, and has a number of wrong fields in it anyway e.g. I don;t have Sale or YearStart fields as per the attached spreadsheet.

I am reading through chap 10 of "QlikView 11 for Developers" to see if this is any good however this is also proving to be hard going due to the arcane syntax.

it would be nice if we could 'debug' & step thru QV formulas - like current 4th gen languages!

Re: YTD calculation in QlikView - Simple Example needed..

Thu, 04 Jul 2013 13:30:52 GMT

CY =num(sum({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}>} LineSalesAmount), '$#,##0;($#,##0)')

LY =num(sum({$<Year = {$(=max(Year-1))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}>} LineSalesAmount), '$#,##0;($#,##0)')

Try This and let me know if any other help is required

Regards

Himanshu

Skype id himanshu_sethi

Re: YTD calculation in QlikView - Simple Example needed..

Gysbert_Wassenaar — Thu, 04 Jul 2013 13:37:02 GMT

You're using the month as a dimension in your chart. That means set analysis expressions are not really an option. You can try using an AsOf table. Read this document: Calculating rolling n-period totals, averages or other aggregations

Re: YTD calculation in QlikView - Simple Example needed..

Fri, 05 Jul 2013 00:27:46 GMT

Thanks, I'll give this & Himanshu's suggestions a shot and read thru the document you link to.

In your experience there fore is the above pivot even possible to create in QlikView - this is actually an attempt to port a SSRS 2005(!) report to QlikView, and honestly I did not expect this to be as complicated.

Thanks for help!

Re: YTD calculation in QlikView - Simple Example needed..

Fri, 05 Jul 2013 02:56:00 GMT

hi again.

having looked through the examples and the code, I don;t think the accumulations beyond month 4 are working on any of the examples? see screenshot below from rolling 3 months.qvw

Whether this is some sort of set up issue particular to my situation I am not sure.

Also, where can I get the salestodate.xls spreadsheet referred to in the AsofTables example? or is this something we need to mock up..

Re: YTD calculation in QlikView - Simple Example needed..

Gysbert_Wassenaar — Fri, 05 Jul 2013 12:21:43 GMT

I don;t think the accumulations beyond month 4 are working on any of the examples?

That's because the example concerns a 3-month rolling total, not an accumulate-forever-after total. The AsOf Table Examples.qvw document contains a YTD example.

The spreadsheet you asked for: salestodate.xls

Re: YTD calculation in QlikView - Simple Example needed..

Tue, 13 Jan 2015 09:58:36 GMT

Is this YTD Calculatios for any data..

if suppose i want my data like gross weight,net amount like this..

please sugggest me

Thanks in advance