Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Not applicable

Re: Sorting TOTAL by group and variable

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
Highlighted
Master II
Master II

Re: Sorting TOTAL by group and variable

Can you try:

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

Highlighted
MVP
MVP

Re: Sorting TOTAL by group and variable

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

Highlighted
Not applicable

Re: Sorting TOTAL by group and variable

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.

Highlighted
Not applicable

Re: Sorting TOTAL by group and variable

Highlighted
Master II
Master II

Re: Re: Sorting TOTAL by group and variable

Can you test this?

Highlighted
Not applicable

Re: Re: Sorting TOTAL by group and variable

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

Highlighted
Not applicable

Re: Re: Sorting TOTAL by group and variable

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

Highlighted
MVP
MVP

Re: Sorting TOTAL by group and variable

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 .

Highlighted
Not applicable

Re: Re: Sorting TOTAL by group and variable

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.