Combine filters - values and total in charts

[PrashantRupani]

Hi ,

I'm trying to get my head around this . 

I have a filter with a field titled Region from the table. That field has values like Region A, Region B .

The user would like to see one of the options in the drop down filter as Total besides Region A, Region B.

So there will be three values user will see in the filter - Total, Region A, Region B. When the user selects Region A, shows sales of Region A , show Region B when that is selected , but when they select Total show the total of Region A and B. 

I have written this below expression in the measure but do not know how to do I get it for total.  It gives me for Region A, B . Please advise. 

SUM({$<Region={"$(=GetFieldSelections(Region))"}>} measure)

[GaryGiles]

What do you mean by a drop down filter?  Are you setting a variable or filtering on a field?  If you are filtering on a field, where the value "Total" come from?

[manoranjan_d · Accepted solution]

try this script

t1:
LOAD sales,
Region

FROM
[C:\Users\NM\Desktop\Scenarios\26 feb\New Microsoft Excel Worksheet.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate
t2:
LOAD * INLINE [
Region
Total
];

Straight table:

dim: region

exp:

=if(GetSelectedCount(Region)=0,sum({<Region=p(Region)>}sales),
if(GetFieldSelections(Region)='RegionA',sum({<Region={'RegionA'}>}sales),
if(GetFieldSelections(Region)='RegionB',sum({<Region={'RegionB'}>}sales),
if(GetFieldSelections(Region)='Total',sum({<Region={RegionA,'RegionB'}>}sales)))))

[PrashantRupani]

Filtering on a field - Total comes from LOAD inline script

Load * inline [

Field_Name

Total,

Region A,

Region B

];

[manoranjan_d]

have you tried above expression or below exp? whether your requirement meet correctly 

=if(GetSelectedCount(Field_Name)=0,sum({<Region=p(Region)>}sales),
if(GetFieldSelections(Field_Name)='Region A',sum({<Region={'RegionA'}>}sales),
if(GetFieldSelections(Field_Name)='Region B',sum({<Region={'RegionB'}>}sales),
if(GetFieldSelections(Field_Name)='Total',sum({<Region={RegionA,'RegionB'}>}sales)))))

[GaryGiles · Accepted solution]

In Qlik Sense, I think you are better off doing this with a variable.  Define variable in your script

Let vSelectRegion = '*';

Using the Variable input control from the Qlik Dashboard bundle, choose vSelectRegion, Dropdown list box and dynamic values with the following =concat(distinct Region&'~'&Region,'|')&'|*~Total'

Then, for you measure expression, use:

SUM({$<Region={'$(vSelectRegion)'}>} measure)

[PrashantRupani]

This worked. Thanks for the solution. Perfect. 

[PrashantRupani]

May I know what will this do ? Unable to understand &'~'& and '|*~Total'.

=concat(distinct Region&'~'&Region,'|')&'|*~Total'

[PrashantRupani]

Let vSelectRegion = '*';

Why to define variable like '*' and what will it do?

[GaryGiles]

The first thing to understand is that the '*' in set analysis means to include all values for the field.

For the variable input object, you can provide a list of values that are fixed by using the "Add Alternatives" button.

Or, you can choose Dynamic and enter/build a string that contains the list of values that you want to present as option in the drop down list.  In the string, you separate options with the "|" character.  So, in case, we could enter the string 'Region A|Region B|*'.  But presenting * is not very user friendly and in your case, you would prefer to show the word "Total".  That's where ~ comes into play.  An ~ tells Qlik to present the 2nd value to the user, but assign the first value to the variable.  So, in this case, we could use a string 'Region A~Region A|Region B~Region B|*~Total'.  Granted, repeating Region A is not necessary, but I do it for consistency and so it is easy to change if needed in the future.

Finally, to make the list dynamic, we use the Concat function to build the list from the data in the field.  So, concat(distinct Region&'~'&Region,'|') basically builds the string 'Region A~Region A|Region B~Region B'.  So, if another region is added to the data, you do not have to adjust the variable input object.  It will be include automatically.  I then add the string '*~Total' to the end to get the Total option in the drop down list.

Hope this helps.