Variable input for modifier

[Qliksense_77]

Hi,

I have data like below. 

I am using "variable input"  for "Colour". For example I have a variable with fixed value: 'black','blue'.

I am searching for a variable with fixed value that sums all rows - also where there is no text in "colour".

Output below is when variable is *. Red is the desired output.

Data:
ID	Month	Colour	Amount
1	january	black	1
2	january	white	1
3	february	red	1
4	march	blue	1
5	march		1
6	march		1
7	march	blue	1
Output:
Month	Sum({<Colour={$(vColour)}>}Amount)	?
january	2	2
february	1	1
march	2	4

[JordyWegman]

Hi,

If it doesn't matter if there is a color, just do: Count(  Month )

Jordy

Climber

[Qliksense_77]

Hi @JordyWegman 

I use variable input in the app with buttons so the user for instans can choose variable defined as 'black','blue'. The function is to select all colours that are black and blue. Therefore I need the vColour in the expression, so my visualisations respond to the users selections.

My problem is that I also need a button in "variable input" that ignores the modifier and display all rows. 

A solution is to use "Filter pane" and If match. Then I get the option to deselect all. I just like the layout of variable input. And it is my first time using variables so I am trying to figure out the limitations and possibilities with this apporach. 

[Sammy_AK]

To get the desired output, use the measure:

Sum({<Colour={$(vColour)}>}Amount) + Sum({-<Colour={$(vColour)}>}Amount) or 

Count({<Colour={$(vColour)}>}Amount) + Count({-<Colour={$(vColour)}>}Amount)

[Qliksense_77]

Hi @Sammy_AK 

It does give the desired output. 

But it will not respond to the variable input. For instans a variable input like 'red' will have no effect on the calculation.

[Sammy_AK]

i am a bit confused. you had requested that you require vColour in the expression. so i have included it. the color red should be driven through vColour. please explain in detail, will try to help or a qvf file. 

[Qliksense_77]

Hi @Sammy_AK 

It is probably just me being a noob. 

Unfortunatly I dont think I am able to get qvf file - I dont have the rights.

So I will try to explain:

I load the data above.

I make a variable called vColour

I add "Variabel input" from the charts menu.

In "variabel input" I use variable "vColour"

In "variable input" I use fixed value.

In "variable input" I type the following in value : 'red'

If I use your expression in a straight table i get the following no matter what I select in variable input: january (2), february (1), march (4).

If I  use this expression the table will respond to "variable input" Sum({<Colour={$(vColour)}>}Amount)

If I select "red" I wil get : february (1)

The problem with this expression is that I cant get a total including the two rows with no colour in march.

Thanks for wanting to help. I really appreciate it:)

[Sammy_AK · Accepted solution]

Thank you for explaining it in detail, I understand you have a variable input column where you have mapped a variable 'vColor' to fixed values like Red, Blue, Black, etc. and when you select the color you get the correct output. additionally you want to add a fixed value in the variable input list which will show you all the totals under color column irrespective if the color value is present or not. 

The problem is that data you have loaded contains blank / NULL values, and they are not calculated. You can handle them while data loading using isnull function or we can use a condition in the measure for calculation.

lets see how to add a condition in measure for calculation, assume that you have added value in the variable input to calculate totals (i.e. in variable input, under fixed value you enter value as * and label as ALL) 

now we need to add a condition to the measure and you measure will look like below. here i am adding a if statement when vColor = 0 then calculate null values, else calculate as per the selected values.  

Sum({<color={$(vColor)}>}amount) +
if(vColor = '*',Sum({-<color={$(vColor)}>}amount),0)

hope this should solve the problem.....

[Qliksense_77]

Thanks a lot. Your solution works perfect both with testdata and the actual app. I very appreciate the help😀