topic Set Analysis in QlikView

Set Analysis

Wed, 11 May 2011 09:49:22 GMT

Hi,

I have a field name index which track the daily index movement. I am trying to show the index movement as a pattern over the year/month/day group. my purpose is to show the end-of-lastday index value in the graph. Example for years selection – Dec 31 values should be on the graph. If particular year is selected – then month end valuesshould come in months, if month is selected actual daily value will be shown ingraph.

As in Set Analysis Chart in the Application .. Dimension is Time Drill and based on that max date has calculated now same max date I am trying to use in set analysis it does notwork for me.

anyone can help me out in this....

Appreciate your response.

regards,

Set Analysis

IAMDV — Wed, 11 May 2011 12:02:34 GMT

I am working on this for you. I'll get back to you soon. Probably sometime tonight!

Cheers - DV

Set Analysis

Thu, 12 May 2011 06:49:57 GMT

Thx D V

Re: Set Analysis

IAMDV — Thu, 12 May 2011 09:01:31 GMT

GM - Here is the idea on how I would do it. I am following this path considering that we are not changing anything in the load script and completely relying on the document expressions. You can also work with the load script to load the hierarchies. However, below is the idea on how you can achieve this with expressions & variables.

I had declared 3 variables, something like this...

vMaxYear : Max({$} Trade_Year)

vMaxMonth : Max({$} Trade_Month)

vMaxDate : Max({$} Trade_Date)

The above variables gives the maximum values for Year, Month & Date. Now we will use the variables in the chart expressions. Here is the sample expression... You need tweak this expression with nested IF's.

Just an example:

=IF( GetFieldSelections(Trade_Date) = ISNULL,

sum({$<Trade_Year = {$(=($(vMaxYear)))}>} [Index]),

sum({$<Trade_Year = {$(=($(vMaxYear)))}, Trade_Date ={'$(=($(vMaxDate)))'}>} [Index])

)

If the Trade_Date does not contain a selection then it should lookup the relevant expression based on your logic. You need to use nested IF's and use the relevant Set Analysis Expressions and the values are calculated based the field selection.

I hope this all makes sense. Let me know if you need further help.

Good luck!

Cheers - DV

Set Analysis

Sun, 15 May 2011 11:27:45 GMT

Thx DV, Let me work it out and will update you.