Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

jcampbell474
Contributor III

Please help with Expression

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.

1 Solution

Accepted Solutions
jcampbell474
Contributor III

Re: Please help with Expression

Sunny, I think I just figured it out.

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

Does it look okay to you?

View solution in original post

7 Replies

Re: Please help with Expression

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

jcampbell474
Contributor III

Re: Please help with Expression

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):

20180222.PNG

Here's what I'm getting:

20180222.PNG

jcampbell474
Contributor III

Re: Please help with Expression

Sunny, I think I just figured it out.

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

Does it look okay to you?

View solution in original post

Re: Please help with Expression

Yes looks good to me....

Luminary
Luminary

Re: Please help with Expression

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

Highlighted
katharinakostka
New Contributor III

Re: Please help with Expression

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.

jcampbell474
Contributor III

Re: Please help with Expression

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

I'll mark it as Answered.