topic Re: Set Analysis in a Chart in QlikView

Set Analysis in a Chart

Sat, 07 Sep 2013 07:47:33 GMT

I'm hoping someone can give me an idea how I could go about producing a solution that would address this situation.

Very simply I have placed an expression in a chart that looks like this:

=(((Sum({<Scenario={'ACTUAL'},[Account List]={'NetAR'}, $(sCM) >}[Balance Sheet Ending Balance])+

Sum({<Scenario={'ACTUAL'},[Account List]={'NetAR'}, $(sTMP) >}[Balance Sheet Ending Balance]))/2)/

Sum({<Scenario={'ACTUAL'},[Account List]={'TradeRev'}, $(sT3M) >} Amount))*$(eCalendarDays(T3M))

In this example I'm calculating "Total Days Outstanding" with sCM equal to Current Month selected, sTMP equal to Three Months Prior and sT3M equal to the total of the 3 months.(eCalendarDays(T3M) is just the number of days for the 3 months in question.

This works fine if I pick one value. However, what I'd like to do is produce a chart with a slider and be able to select any number of months and show this value as a trend relative to each month that I pick in my slider. What is tricky is that for each month I select in the multi-select slider it has to perform this calculation with that particular month in mind rather than just from the standpoint of a "current month"/1 month in particular.

Any ideas would be much appreciated.

Thanks,

Neil

Re: Set Analysis in a Chart

rajeshvaswani77 — Sat, 07 Sep 2013 08:04:22 GMT

Hi Neil,

At the high level.

1)come up with a min variable which will hold the minimum date that you need for your solution

2)come up with a max variable which will hold the maximum date that you need for your solution

3)Assign these to min and max variables in the slider.

4)Create a third variable that you will point to in the slider.

5)This variable will get the date assigned when the slider moves between the min and max

6)Use this third variable in your set analysis in place of sCM

7)Instead of sT3M, have sCM - 3 months in set expression.

thanks,

Rajesh Vaswani

Re: Set Analysis in a Chart

Wed, 11 Sep 2013 04:43:00 GMT

Thanks Rajesh,

The issue I have is that I want to select multiple months on the slider and have this calculation occur for each month that I select. Basically, I want to perform what you see below individually on every 'Month-Year'(Month Overall) selection that is selected on the slider and then display it in a chart. Obviously the use of "max" won't work here, but I just wanted to show you an example of what I'm really trying to do. Any ideas?

Sum({<Scenario={'ACTUAL'},[Account List]={'NetAR'},[MMR Version], [%Month Overall]= {"$(=max([%Month Overall]))"} >}[Balance Sheet Ending Balance])+

Sum({<Scenario={'ACTUAL'},[Account List]={'NetAR'},[MMR Version], [%Month Overall] = {"$(=max([%Month Overall]-3))"} >}[Balance Sheet Ending Balance]))/2)/

(Sum({<Scenario={'ACTUAL'},[Account List]={'TradeRev'},[MMR Version], [%Month Overall] = {"$(=max([%Month Overall]-2))"} >}[Amount])+

Sum({<Scenario={'ACTUAL'},[Account List]={'TradeRev'},[MMR Version], [%Month Overall] = {"$(=max([%Month Overall]-1))"} >}[Amount])+

Sum({<Scenario={'ACTUAL'},[Account List]={'TradeRev'},[MMR Version], [%Month Overall] = {"$(=max([%Month Overall]))"} >}[Amount])))*92