QlikView App: Simple Year To Date Example (Set Analysis)

[stevedark]

This QlikView shows how basic Set Analysis syntax can be used to deliver a view of Year To Date figures.

Two subtly different approaches are given, one using a Pivot table to give the months as a dimension, the other using a Straight Table.  The first approach is the quickest and easiest to implement - whilst the other gives more flexibility (in this case to add both Total and Average columns).

The QlikView was originally written as a response to the following QlikCommunity thread: http://community.qlik.com/message/132639

Steve Dark

www.quickintelligence.co.uk

[#2]

this is good for users. i really appreciate

[stevedark]

Many thanks Chandana.

If you want to see some of the other QlikView examples I have uploaded please take a look here:

http://www.quickintelligence.co.uk/qlikview-examples/

Steve

[#4]

Noted with thanks

[#5]

In the YTD Total, Is it possible to keep the same functionality but hide some of the months based on the selection keeping the same total. I have tried the conditional on the expression but have no been successful. Thanks

[stevedark]

With a pivot the total will always be the total of what is shown.  When you hide things they can break.

You may find some inspiration in this blog post:

http://www.quickintelligence.co.uk/qlikview-accumulate-values/

It discussing joining to a separate dimension to accumulate.  You would join Jan 2015 to a new dimension called Jan 2015, then Jan and Feb 2015 into a new Feb 2015 dimension.  All months in a year would then join in to a dimension called 2015 Total.  This way the total will always contain the total, regardless of which other columns are shown.  This is kind of a hybrid of the Average Bar and MAT techniques I explain in the attached blog.

Another technique would be to use a straight table with 13 expressions, each doing Set Analysis to load YTD for a that month.  Conditional show on columns would then work.  When creating numerous expressions like that it is worth always copying and pasting the expressions:

http://www.quickintelligence.co.uk/two-tips-improve-qlik-life/

Hope that helps.

Steve

[#7]

muchas gracias, una gran ayuda,

saludos

Kary

[ecolomer]

Lo mismo digo. Buen trabajo.

[stevedark]

Gracias Enrique!

[qlikviewwizard]

Good document. Thank you for info.

[isaaclin]

When I just started with Qlik sense, the set analysis was a road block for me to conquer, especially on the manipulating date. This simple tutorial clarified a lof of my questions on this area, a good job to the author.