topic Comparing years without using set analysis in QlikView

Comparing years without using set analysis

oxiofrt — Wed, 16 Dec 2009 20:53:11 GMT

Hi,

I would like to create a chart displaying an indicator / year. A new column should contain the value for the previous year. As soon as I wouldn't like to force a selection, it should work without using set analysis. It should also work if the sort on year change (should therfore not use below and above).
Does a formula like that exists in Qlikview :
Indicator N-1 = value of indicator for the dimension minus 1

Year Indicator Indicator N-1

2007 90 -

2008 100 90

2009 143 100

Comparing years without using set analysis

Wed, 16 Dec 2009 21:41:08 GMT

Is that what you need ?

(look in inter-records Expressions functions like above).

Best regards

Comparing years without using set analysis

oxiofrt — Wed, 16 Dec 2009 21:51:48 GMT

Thanks a lot for your answer.

But it is not what I need as the use of above function doesn't work anymaore as soon as the sorting on column année is changed.

I am looking for a function which would not be dependant from the sort but would return a value for the value of the dimension minus 1

Comparing years without using set analysis

Wed, 16 Dec 2009 22:02:45 GMT

Sorry for the misunderstanding :),

In that case, why don't you want to use set analysis ? I think it should be the perfect solution to your problem ??

If it's just a syntax problem I can help you

Comparing years without using set analysis

oxiofrt — Wed, 16 Dec 2009 22:12:00 GMT

Well, to be honest I have no problem with the syntax but don't really agree with the principle which is just opposite to Qlikview nature...

As soon as I use set analysis, I have to force some selection to be available (in the exemple, a year should always be selected to be able to use this selected year as a reference). It implies to change the whole method of achieving an application.

Thanks however for your help. Are you a fan of set analysis ? I am not, if so I would probably sell Business Objects ... (but I really prefer Qlikview 🙂 )

Comparing years without using set analysis

Wed, 16 Dec 2009 22:36:17 GMT

Okay no problem :),

just one or two ideas :

- try if it's possible to do something with fieldindex or fieldvalue functions

- use the peek (or other inter record functions) in the script to duplicate your result by creating a new field representing the previous year value. That idea depend on your data model.

Regards

Comparing years without using set analysis

oxiofrt — Wed, 16 Dec 2009 23:23:20 GMT

Thanks a lot for your help.

Bye

Comparing years without using set analysis

johnw — Thu, 17 Dec 2009 06:04:45 GMT

If I understood your question, set up an "Indicator Year" completely disconnected from your data. Use that as your dimension instead of the Year field. Then do this:

count(if(ANNEE="Indicator Year" ,ID_INTERVENTION))
count(if(ANNEE="Indicator Year"-1,ID_INTERVENTION))

It'll be slow on large data sets, and won't display years that your selections have excluded, but I think it's what you're looking for. If performance is a big concern, or you want to still display the previous year even when only one year is selected, there are ways around it other than set analysis. See attached for a way to trade some complexity and RAM for significantly-improved performance.

Comparing years without using set analysis

Thu, 17 Dec 2009 08:03:44 GMT


oxioFRT wrote:
Well, to be honest I have no problem with the syntax but don't really agree with the principle which is just opposite to Qlikview nature...
As soon as I use set analysis, I have to force some selection to be available (in the exemple, a year should always be selected to be able to use this selected year as a reference). It implies to change the whole method of achieving an application.
Thanks however for your help. Are you a fan of set analysis ? I am not, if so I would probably sell Business Objects ... (but I really prefer Qlikview 🙂 )<div></div>

oxio,

Actually, this isn't necessarily true. In the application below, one of our consultants demonstrates how to do period calculations based on flags (pre-Set Analysis), standard Set Analysis (which, like you describe, requires a user to select periods to compare), and most importantly, something he calls "Advanced Dynamic Set Analysis", which works regardless of selections. Please download it and let us know what you think:

http://community.qlik.com/media/p/82595.aspx

Comparing years without using set analysis

oxiofrt — Tue, 29 Dec 2009 17:03:07 GMT

Well, it's just what I was looking for.
Dynamic set analysis is pretty simple to realize and really useful.

Thanks a lot !