topic Re: Please help with Expression in QlikView

Please help with Expression

jcampbell474 — Thu, 22 Feb 2018 21:01:46 GMT

I'm trying to create a metric (Attach Rate %). A good bit of labor would be involved if I modified the existing data model, so I've been trying to do it as an expression.

I have a field DGFlag that is populated with a 1 on each row of DG Product.

I have another field PPA_Flag that is populated with a 1 in every row with PPA Product (also, in the state of VA).

Problem is, each DGFlag is assocated with a DG product, so I can't sum the PPA_Flag as it's not associated with the DG product. I've tried to 'break' the association and get a sum, but haven't had any luck. I tried set analysis, but it only caclulates once, per object. Also tried Sum(Total)... No luck.

Can anyone tell me if what I'm trying to do is possible?

Here are some expressions that I tried:

sum({<State={'VA'}>} DGFlag) / sum(TOTAL PPA_Flag)

sum({<State={'VA'}>} DGFlag) / sum({<product=p({<product=p(product)>}product)>}PPA_Flag)

sum({<State={'VA'}>} DGFlag) / (avg({<State={'VA'},product=>}PPAWeeklyTotal))

Thank you in advance for any/all help.

Re: Please help with Expression

sunny_talwar — Thu, 22 Feb 2018 21:12:10 GMT

But what exactly are you trying to do? I am not sure I get that from your post?

Re: Please help with Expression

jcampbell474 — Thu, 22 Feb 2018 21:22:20 GMT

I should have mentioned that the DG Product is a dimension (as is WeekCur). That's why I'm having issues trying to sum the PPA_Flag - because it's not associated with DG Product.

Here's what it should be (without the DG Product dimension):

Here's what I'm getting:

Re: Please help with Expression

jcampbell474 — Thu, 22 Feb 2018 21:27:44 GMT

Sunny, I think I just figured it out.

sum({<State={'VA'}>} DGFlag) / sum(TOTAL <WeekCur> PPA_Flag)

Does it look okay to you?

Re: Please help with Expression

sunny_talwar — Fri, 23 Feb 2018 02:57:43 GMT

Yes looks good to me....

Re: Please help with Expression

shilpan — Fri, 23 Feb 2018 03:26:25 GMT

Not sure if you need this TOTAL <WeekCur> since you only have one dimension.

Re: Please help with Expression

katharinakostka — Fri, 23 Feb 2018 07:11:22 GMT

Shilpan: But without the TOTAL <WeekCur> it didn't work.

As Jason mentioned that DG_Product is a Dimension as well, so he has two dimensions. he doesn't show it, but it might as well be affecting in the backgound. Also it might be, that he will add others as well when this is progressing.

There's nothing wrong with the TOTAL <WeekCur>...though I see your point and in general it is correct.

Question to Jason would be: Does it work as expected now? If so, please mark the post with the correct answer (even if in this case it came from yourself), so that others can see, that this is solved. If not, please tell us as well, so we might look into it further.

Re: Please help with Expression

jcampbell474 — Fri, 23 Feb 2018 14:17:07 GMT

Yes, it works as expected. Thanks to everyone for their input.

I'll mark it as Answered.