topic Re: Re: Sorting TOTAL by group and variable in QlikView

Sorting TOTAL by group and variable

Mon, 18 Aug 2014 06:25:00 GMT

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

Re: Sorting TOTAL by group and variable

ecolomer — Mon, 18 Aug 2014 07:35:36 GMT

Can you try:

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

Re: Sorting TOTAL by group and variable

tresB — Mon, 18 Aug 2014 07:43:17 GMT

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

Re: Sorting TOTAL by group and variable

Mon, 18 Aug 2014 08:50:33 GMT

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.

Re: Sorting TOTAL by group and variable

Mon, 18 Aug 2014 10:28:32 GMT

Here's the sample

http://www.kaksonen.org/test2.qvw

Re: Re: Sorting TOTAL by group and variable

ecolomer — Mon, 18 Aug 2014 20:00:09 GMT

Can you test this?

Re: Re: Sorting TOTAL by group and variable

Tue, 19 Aug 2014 04:55:24 GMT

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

And heres the same selected from the AD_Campaign field

Re: Re: Sorting TOTAL by group and variable

Tue, 19 Aug 2014 05:30:38 GMT

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

Re: Sorting TOTAL by group and variable

tresB — Tue, 19 Aug 2014 05:55:47 GMT

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 .

Re: Re: Sorting TOTAL by group and variable

Tue, 19 Aug 2014 06:16:34 GMT

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.

Re: Sorting TOTAL by group and variable

tresB — Tue, 19 Aug 2014 06:24:04 GMT

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.

Re: Sorting TOTAL by group and variable

Tue, 19 Aug 2014 08:11:17 GMT

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