Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sorting TOTAL by group and variable

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

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

11 Replies
ecolomer
Master II
Master II

Can you try:

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

tresesco
MVP
MVP

AD_CampaignID={'$(vStudy)'  // without an '=' should work alone. Could you post a sample qvw and explain the expected output against that?

Not applicable
Author

Hi,

I posted a sample qvw, which should show up, once it get's through moderation.

The main idea with the vStudy variable is that I want to use it instead of selection from the AD_CampaignID field so that it's value doesn't show up in the selected fields and it doesn't get cleared when a user clears all the selections.

Not applicable
Author

ecolomer
Master II
Master II

Can you test this?

Not applicable
Author

Doesn't seem to work any better.

Setting the vStudy variable as 1001 should give to the graph the same result as selecting 1001 from the AD_CampaignID field. The only difference would be that with the variable the 1001 the user cannot clear it from the selections by pressing a button.

Here's the 1001 selected by the variable

Snip1.JPG.jpg

And heres the same selected from the AD_Campaign field

Snip2.JPG.jpg

Not applicable
Author

The expression that I'm trying to achieve should look without the formulas something in the style

Sum(<AD_CampaignID=1001, AD_NoticeScore1=1>Weight)/Sum(TOTAL <AD_CampaignID=1001, Gender> Weight).

Is it even ok, to have both a 'Field =' and a dimension (Gender) specified in the same selection ?

Jokke

tresesco
MVP
MVP

Try this:

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

Note: getcurrentfield() would not be required because TOTAL key would by default act upon current field .

Not applicable
Author

tresesco,

no luck it gives a too low percentage. It seems to divide by all the answers of the study in question, when for example selecting Gender, I would like it to divide separately by all the females and all the males.

So this formula works correctly in the situation when you have Gender selected and it works only for the females but I would like to use the cyclic group to be able to use the same formula for several different dimensions.

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

So instead of the Gender={'F'} have the functionality that the [$(=GetCurrentField(SelectNoticeScoreGroup1))] allows.