Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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!
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
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!
Hiya
I cant get the
Let vExpression= { Catering , Homeware } to work in my data load
please help
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))
)
)
this does not work, I have made sure the Category is correctly spelt as its wrong in the expression, but still no joy
Hi Joanna,
Is it possible to attach the sample file?
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