Trend Line only appears when filter selected

[joeybird]

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

[JonnyPoole]

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

[reddy-s]

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]

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

[reddy-s]

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]

Hiya

I cant get the

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

please help

[reddy-s]

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]

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

[reddy-s]

Hi Joanna,

Is it possible to attach the sample file?

[JonnyPoole]

‌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