Please help with Expression

[jcampbell474]

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.

[sunny_talwar]

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

[jcampbell474]

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:

[jcampbell474 · Accepted solution]

Sunny, I think I just figured it out.

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

Does it look okay to you?

[sunny_talwar]

Yes looks good to me....

[shilpan]

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

[katharinakostka]

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]

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

I'll mark it as Answered.