Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

joeybird
Contributor II

Trend Line only appears when filter selected

Hiya

I have a line charts showing total number of sales per month

when I select from a filter pane - Department - Catering...

the line changes to the total number of sales for that department, which is correct...

however.

I want the line charts showing total number of sales per month

and an additional line for  the total number of sales for that department

I can do this expression, but the line is only to appear if a department is selected.

any ideas on show / hide function?

please help

15 Replies
Employee
Employee

Re: Trend Line only appears when filter selected

Joanna i thought of a way to do this:

1. Create a line chart with month as the dimension

2. Create a 2nd dimension with this expression:  Valuelist('MonthlySales','TotalDepartmentSales')

3. Create a measure with this expression:

if( Valuelist('MonthlySales','TotalDepartmentSales') = 'MonthlySales', sum(Sales),

     if( Valuelist('MonthlySales','TotalDepartmentSales') = 'TotalDepartmentSales',

          if( getselectedcount(Department) > 0 , sum( total Sales) )

     )

)

this suppressed the 2nd line when there wasn't a department selected

reddys310
Honored Contributor II

Re: Trend Line only appears when filter selected

Hi Joanna,

I have implemented a sample similar to this for an other post. Its exactly was Jonathan clearly mentioned.

Check this out : Line charts with dynamic / filterable measures

Hope this helps!

joeybird
Contributor II

Re: Trend Line only appears when filter selected

Hiya

this does work fab, but how do you get a third line to appear ?

Catering - so total line appears , (like it does using code above) then someone chooses additionally, Home ware, so a third line appears? for Home ware total? 

Please help

reddys310
Honored Contributor II

Re: Trend Line only appears when filter selected

Hi Joanna,

Yes, when someone selects "Home ware", you can plot the third line as well by following the same procedure. In the second measure add , "Home ware" to the value list and modify the measure to :

Let vExpression= { Catering , Homeware } - a variable

Expression: 

if( Valuelist('MonthlySales','TotalDepartmentSales') = 'MonthlySales', sum(Sales),

     if( Valuelist('MonthlySales','TotalDepartmentSales') = 'TotalDepartmentSales',

          PICK( Match('$(vExpression)',getcurrentselection(<field>)) , sum({< set expression >} total Sales), sum({< set expression >} total Sales))

     )

)

If you can attach a sample qvf it would be more easy to solve it!

joeybird
Contributor II

Re: Trend Line only appears when filter selected

Hiya

I cant get the

Let vExpression= { Catering , Homeware } to work in my data load

please help

reddys310
Honored Contributor II

Re: Trend Line only appears when filter selected

Hi Joanna,

Simple use this instead of the Let statement:

measures:

LOAD * INLINE [

    Category

    Catering

    Homeware

];

replace the $(vExpression) with the field: [Category]

if( Valuelist('MonthlySales','TotalDepartmentSales') = 'MonthlySales', sum(Sales),

     if( Valuelist('MonthlySales','TotalDepartmentSales') = 'TotalDepartmentSales',

          PICK( Match(Catgory,getcurrentselections(<field>)) , sum({< set expression >} total Sales),sum({< set expression >} total Sales))

     )

)

joeybird
Contributor II

Re: Trend Line only appears when filter selected

this does not work, I have made sure the Category is correctly spelt as its wrong in the expression, but still no joy

reddys310
Honored Contributor II

Re: Trend Line only appears when filter selected

Hi Joanna,

Is it possible to attach the sample file?

Employee
Employee

Re: Trend Line only appears when filter selected

‌I think you need a 3rd value in the valuelist() to get a 3rd line:

if( valuelist('a','b','c') = 'a' , <expression1>,

  if (valuelist('a','b','c') = 'b' , <expression2>,

etc...

as as as we did before you can add other conditions using AND to conditionally show the 2nd and/or 3rd line

Community Browser