topic Re: How to use multiple sums in Set Analysis in QlikView

How to use multiple sums in Set Analysis

hansdevr — Tue, 12 Jan 2016 09:50:25 GMT

Hi Everyone,

I'm working on a dashboard in which a decline/accept percentage on damage claims is calculated.

For one date it's not that hard: sum(Toewijzing) / (sum(Toewijzing) + sum(Afwijzing))

Each record in my table has a 0 / 1 or a 1 / 0 value for these two fields.

Now I want to plot the acceptance percentage in a graph, showing this year and the past 3 years (Y-axis percentage, X-axis months). I want to give it a try using set analysis (to which I'm sort of a newbie), but I wonder how to do this, as multiple sums in one statement are not possible.

Anyone any idea how I should go about this?

Re: How to use multiple sums in Set Analysis

Chanty4u — Tue, 12 Jan 2016 10:38:39 GMT

sum(Toewijzing) / (sum(Toewijzing) +(Afwijzing))

Re: How to use multiple sums in Set Analysis

hansdevr — Tue, 12 Jan 2016 11:15:05 GMT

shureshqv, thanks, but this is just another way of writing the same thing.. I would like to know how to translate this to a set analysis expression.

Re: How to use multiple sums in Set Analysis

santhosh_k_n — Tue, 12 Jan 2016 12:28:09 GMT

Hi,

U can use as follows

for static values as Sum({<Year={'2016','2015','2014','2013'}>}Toewijzing) [use same set analysis in rest of expressions]

for dynamic year values

set variable V_Year= year(today)-4

sum({<year={'>=$(=V_Year)'}>}Toewijzing)

Hope this helps

Regards,

Re: How to use multiple sums in Set Analysis

hansdevr — Tue, 12 Jan 2016 12:41:20 GMT

I'm afraid that won't do it, because I want to display the percentage of 'Toewijzing' with regard to the total of 'Toewijzing' + 'Afwijzing' (acceptance and decline).

Re: How to use multiple sums in Set Analysis

hansdevr — Tue, 12 Jan 2016 12:48:52 GMT

I came up with this, which seems to work. Only thing is, it seems to be ridiculously long and I have the feeling it could be much more simple:

= 100 * (
Sum({1< [Schadecategorie] = P([Schadecategorie]), [Schadesoort] = P([Schadesoort]), [Jaar besluit]={$(vDitJaar)}>}[Toewijzing])

/

(
Sum({1< [Schadecategorie] = P([Schadecategorie]), [Schadesoort] = P([Schadesoort]), [Jaar besluit]={$(vDitJaar)}>}[Toewijzing])

+

Sum({1< [Schadecategorie] = P([Schadecategorie]), [Schadesoort] = P([Schadesoort]), [Jaar besluit]={$(vDitJaar)}>}[Afwijzing])
)
)

Re: How to use multiple sums in Set Analysis

HirisH_V7 — Tue, 12 Jan 2016 13:01:44 GMT

Hi,

Your Expression can be like this,

sum({<Year={'>=$(=Max(year)-4)'}>}Toewijzing) / (sum({<Year={'>=$(=Max(year)-4)'}>}Toewijzing) +Sum({<Year={'>=$(=Max(year)-4)'}>}Afwijzing))

Hope this helps,

Hirish

Re: How to use multiple sums in Set Analysis

PradeepReddy — Tue, 12 Jan 2016 13:07:12 GMT

sum({$<Year={'> $(=max(Year)-3) <= $(=max(Year))'}>}Toewijzing)/(sum({$<Year={'> $(=max(Year)-3) <= $(=max(Year))'}>}Toewijzing)+sum({$<Year={'> $(=max(Year)-3) <= $(=max(Year))'}>}Afwijzing))

Re: How to use multiple sums in Set Analysis

hansdevr — Tue, 12 Jan 2016 14:52:54 GMT

Thnx, pradeep,

I will try this ASAP and report back here!

Re: How to use multiple sums in Set Analysis

hansdevr — Tue, 12 Jan 2016 14:53:36 GMT

Thnx, Hirish,

I will try this ASAP and report back here!

Re: How to use multiple sums in Set Analysis

hansdevr — Wed, 13 Jan 2016 07:43:51 GMT

Both solutions worked, thank you both very much!!

I was especially happy to see, that both solutions came close to what I suggested, so my train of thought was in the right direction.

Unfortunately, I could only mark one solution as correct....