Expression built from selections

[raynac]

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!

[Anonymous]

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)

[raynac]

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.

[Anonymous]

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

[raynac]

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? 

[Anonymous · Accepted solution]

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...

[raynac]

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!