topic Re: Set Analysis in QlikView

Set Analysis

Thu, 02 Jul 2015 17:03:13 GMT

I am took over a project and they have this set analysis, =$(=concat(distinct 'sum({< claim_coverage= >} [ep_' & lower(Premium_Coverage) & '])', ' + ' )) . I need to times something to this where it will filter according to coverage and was wondering how to go about this. Any help would be great. I have tried sum(commission_pl * $(=concat(distinct 'sum({< claim_coverage= >} [ep_' & lower(Premium_Coverage) & '])', ' + ' )) and =$(=concat(distinct 'sum({< claim_coverage= >} [ep_' & lower(Premium_Coverage) & ']) *commission_pl', ' + ' )). Nothing seems to be working and I was wondering if any of you have any ideas on how to fix this?

thanks

Re: Set Analysis

ramoncova06 — Thu, 02 Jul 2015 17:19:03 GMT

what kind of coverage do you want to filter on ?

you have two in the expression, and you are excluding the filtering on claim_coverage

Re: Set Analysis

Gysbert_Wassenaar — Thu, 02 Jul 2015 17:20:51 GMT

I need to times something to this where it will filter according to coverage

You need to what?

Re: Set Analysis

Thu, 02 Jul 2015 17:21:54 GMT

There are multiple coverages, bi, apa, biic, bp, and cpl are just a few to list. With commission we don't have that so I would like commissions to be applied to all of these so when I sum over a time period I will get the correct number. Does that make sense?

Re: Set Analysis

Thu, 02 Jul 2015 17:25:43 GMT

I have premium earned over different coverages. So to sum everything or just the specific coverage that the user picks (ep_bi, ep_apa, ....) as so forth it will only sum that premium over those specific coverages. I now want to find my commissions which are a rate applied to these premium. So I need that commission ratio to be applied to that set analysis to be able to get the commissions by coverage. Does that make sense?

Thanks

Re: Set Analysis

ramoncova06 — Thu, 02 Jul 2015 18:43:29 GMT

kind of ... so you are looping thru all of the Premium_Coverages and then adding them up, but I don't get where do you want to filter them the coverage, to me filtering means only showing some selected few and ignoring others, I don't get what does multiplying the commissions has to do with filtering

I am assuming that you want to multiply the commission by the total of the sum, right ?

do you have a single commission or several different ones ? if you have a single commision you can multiply it at the end of the expression

=$(=concat(distinct 'sum({< claim_coverage= >} [ep_' & lower(Premium_Coverage) & '])', ' + ' ))*Commision

if you have a different commision by Premium Coverage you can add it at the end of the last "]"

=$(=concat(distinct 'sum({< claim_coverage= >} [ep_' & lower(Premium_Coverage) & ']*Commision)', ' + ' ))

Re: Set Analysis

Gysbert_Wassenaar — Fri, 03 Jul 2015 06:30:03 GMT

I'm afraid not. Try explain it to me in short sentences with a bit more punctuation in just the right places and try to give an example with some numbers.