Sorting TOTAL by group and variable

[#1]

Hi,

Having a small problem with finding the correct syntax.

I have this expression that works nicely

Sum({$<AD_NoticeScore1={'1'}>}Weight)/Sum(TOTAL <[$(=GetCurrentField(SelectNoticeScoreGroup1))]> Weight)

Now I would need to add the functionality to sort the result by the variable study.

AD_CampaignID={'$(=vStudy)'}

So it should look something like

Sum({$<AD_CampaignID={'$(=vStudy)'}, AD_NoticeScore1={'1'}>}Weight)/Sum(TOTAL <AD_CampaignID={'$(=vStudy)'},[$(=GetCurrentField(SelectNoticeScoreGroup1))]> Weight)

The first sum part works nicely but the TOTAL syntax doesn't. Help would be greatly appreciated, thanks in advance.

Jokke Kaksonen

[tresB]

Jokke Kaksonen wrote:

no luck it gives a too low percentage...

This is beacuse, your divisor is not being filtered by the AD_NoticeScore1={'1'} which is the case with dividend. If you use the same filter in both parts you would get F+M=100. Try:

Sum({$<AD_CampaignID={'$(vStudy)'}, AD_NoticeScore2={'1'}>}Weight)/Sum(TOTAL {<AD_CampaignID={'$(vStudy)'},AD_NoticeScore2={'1'}>} Weight)

Not sure, If I really got your issue right.

[#3 · Accepted solution]

I finally figured out the correct formula. It is:

Sum({$<AD_CampaignID={'$(=vStudy)'},AD_NoticeScore1={'1'}>}Weight)/Sum(TOTAL <[$(=GetCurrentField(SelectNoticeScoreGroup1))]> {$<AD_CampaignID={'$(=vStudy)'}>}Weight)

Thank you all for your effort and time

Jokke