article QlikView App: Simple Year To Date Example (Set Analysis) in Member Articles

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

stevedark — Wed, 29 May 2013 23:04:25 GMT

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

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

Sat, 11 Oct 2014 07:21:08 GMT

this is good for users. i really appreciate

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

stevedark — Sat, 11 Oct 2014 07:37:44 GMT

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

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

Sun, 12 Oct 2014 06:52:59 GMT

Noted with thanks

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

Fri, 19 Dec 2014 16:01:50 GMT

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

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

stevedark — Fri, 19 Dec 2014 16:26:47 GMT

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

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

Thu, 12 Feb 2015 19:05:13 GMT

muchas gracias, una gran ayuda,

saludos

Kary

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

ecolomer — Sun, 02 Aug 2015 09:16:06 GMT

Lo mismo digo. Buen trabajo.

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

stevedark — Sun, 02 Aug 2015 21:37:26 GMT

Gracias Enrique!

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

qlikviewwizard — Mon, 03 Aug 2015 03:45:27 GMT

Good document. Thank you for info.

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

isaaclin — Wed, 01 Jun 2016 17:30:00 GMT

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.

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

stevedark — Wed, 01 Jun 2016 17:44:52 GMT

Thank you Isaac, it's very kind of you to say that.

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

Anonymous — Thu, 28 Sep 2017 11:43:37 GMT

Thank you, but do you know if is applicable also in QlikSense ? thanks

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

stevedark — Thu, 28 Sep 2017 13:25:27 GMT

Yes, this would work the same. You can use the QlikView Converter helper in the Dev Hub to bring in the example QVW and then go from there. This example app I have shared is in Sense, and uses similar techniques:

Qlik Sense App: Prior Period Comparison with Set Analsysis