topic Re: Expression built from selections in QlikView

Expression built from selections

raynac — Fri, 20 Feb 2015 20:42:27 GMT

I built a hardcoded _Dimension and need to use it to create a summary in a chart. I want to allow my clients to choose which revenue elements to include in their summary.

Here is the expression I built, but it's not working. If I select more than one item, I get "No Data To Display". Can anyone tell me what I might be doing wrong?

if(_Dimension='Activity', Sum ([Room Night ActivityRev]),0) +

if(_Dimension='Other Charge', Sum ([Room Night OtherChargeRev]),0) +

if(_Dimension='Shuttle', Sum ([Room Night ShuttleRev]),0) +

if(_Dimension='Spa', Sum ([Room Night SpaRev]),0) +

if(_Dimension='Package', Sum ([Room Night PkgRev]),0) +

if(_Dimension='Facility', Sum ([Room Night FacilityRev]),0) +

if(_Dimension='Alternate Inventory', Sum ([Room Night AltInvRev]),0) +

if(_Dimension='Other', Sum ([Room Night OtherRev]),0)

Thank you in advance for any help you can give!

Re: Expression built from selections

Anonymous — Fri, 20 Feb 2015 21:18:46 GMT

Because if there is more than one value is selected/available, you cannot say if the condition in if() is true or false. Try this:

if(wildmatch(GetFieldSelections(_Dimension),'*Activity*'), Sum ([Room Night ActivityRev]),0) +
if(wildmatch(GetFieldSelections(_Dimension),'*Other Charge'*), Sum ([Room Night OtherChargeRev]),0) +
...
if(wildmatch(GetFieldSelections(_Dimension),'*Other*'), Sum ([Room Night OtherRev]),0)

Re: Expression built from selections

raynac — Fri, 20 Feb 2015 21:45:47 GMT

Hi Michael,

Thank you SO much. I implemented this and I thought it was working great until I clicked on all the selections and something odd happened. Qlikview seems fine with the first six (no matter what six those happen to be). The minute I choose a seventh option, or select All, it no longer works. I get the same "No Data to Display" message. If I deselect any two of the eight options, the data reappears. Isn't that odd?

if(wildmatch(GetFieldSelections(_Dimension),'*Activity*'), Sum ([Room Night ActivityRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension),'*Other Charge*'), Sum ([Room Night OtherChargeRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension),'*Shuttle*'), Sum ([Room Night ShuttleRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension),'*Spa Services*'), Sum ([Room Night SpaRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension),'*Package*'), Sum ([Room Night PkgRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension),'*Facility*'), Sum ([Room Night FacilityRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension),'*Alternate Inventory*'), Sum ([Room Night AltInvRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension),'*Other Revenue*'), Sum ([Room Night OtherRev]),0)

I thought it was me and I went in and changed some _Dimension options and the Expression, but no dice.

Re: Expression built from selections

Anonymous — Fri, 20 Feb 2015 21:50:26 GMT

Sorry, missed this.

It works as the selection box, by default shows up to six values, and shows count after that. But there is a parameter to avoid this:

GetFieldSelections(_Dimension, ', ', 10)

Now you can have up to 10 values in this example

Re: Expression built from selections

raynac — Fri, 20 Feb 2015 22:12:38 GMT

Hi Michael,

I feel stupid, but I cannot get what you are saying to work.

First I tried this:

if(wildmatch(GetFieldSelections(_Dimension),'*Activity*', 10), Sum ([Room Night ActivityRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension),'*Other Charge*', 10), Sum ([Room Night OtherChargeRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension),'*Shuttle*', 10), Sum ([Room Night ShuttleRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension),'*Spa Services*', 10), Sum ([Room Night SpaRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension),'*Package*', 10), Sum ([Room Night PkgRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension),'*Facility*', 10), Sum ([Room Night FacilityRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension),'*Alternate Inventory*', 10), Sum ([Room Night AltInvRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension),'*Other Revenue*', 10), Sum ([Room Night OtherRev]),0)

And then JUST in case, I tried this:

if(wildmatch(GetFieldSelections(_Dimension, 10),'*Activity*'), Sum ([Room Night ActivityRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension, 10),'*Other Charge*'), Sum ([Room Night OtherChargeRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension, 10),'*Shuttle*'), Sum ([Room Night ShuttleRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension, 10),'*Spa Services*'), Sum ([Room Night SpaRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension, 10),'*Package*'), Sum ([Room Night PkgRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension, 10),'*Facility*'), Sum ([Room Night FacilityRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension, 10),'*Alternate Inventory*'), Sum ([Room Night AltInvRev]),0) +

if(wildmatch(GetFieldSelections(_Dimension, 10),'*Other Revenue*'), Sum ([Room Night OtherRev]),0)

I then figured I was going about this wrong; so I tried adding GetFieldSelections(_Dimension, ', ', 10) to the beginning of the expression and that gives an error so I still cannot figure it out.

So, I do apologize but I must ask...where does "GetFieldSelections(_Dimension, ', ', 10)" go?

Re: Expression built from selections

Anonymous — Fri, 20 Feb 2015 23:47:59 GMT

What you tried is not that I said... Here it is:

if(wildmatch(GetFieldSelections(_Dimension, ', ', 10),'*Activity*'), Sum ([Room Night ActivityRev]),0) +
if(wildmatch(GetFieldSelections(_Dimension, ', ', 10),'*Other Charge'*), Sum ([Room Night OtherChargeRev]),0) +
...

...
if(wildmatch(GetFieldSelections(_Dimension, ', ', 10),'*Other*'), Sum ([Room Night OtherRev]),0)

To see what it does, create a text box with expression

=GetFieldSelections(_Dimension)

and another text box with

=GetFieldSelections(_Dimension, ', ', 10),

and make selections, selecting fewer than 6 and greater than 6 values. You'll see it right away

BTW, I assumed that you have only eight values in the _Dimension field, so limit 10 is more than enough. If there are more. just replace this 10 with a greater number.

I hope it works this time...

Re: Expression built from selections

raynac — Mon, 23 Feb 2015 14:01:31 GMT

Thank you so much for your patience with me! I am afraid that even though I have been working with the program for several years, I have only just started doing things other than the basics recently.

This worked like a charm, once I understood what you were saying!

Thank you again!