topic Trend Line only appears when filter selected in App Development

Trend Line only appears when filter selected

joeybird — Thu, 26 Nov 2015 16:26:04 GMT

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

Re: Trend Line only appears when filter selected

JonnyPoole — Thu, 26 Nov 2015 21:37:24 GMT

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

Re: Trend Line only appears when filter selected

reddy-s — Fri, 27 Nov 2015 09:57:34 GMT

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!

Re: Trend Line only appears when filter selected

joeybird — Fri, 27 Nov 2015 10:55:38 GMT

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

Re: Trend Line only appears when filter selected

reddy-s — Fri, 27 Nov 2015 11:17:51 GMT

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!

Re: Trend Line only appears when filter selected

joeybird — Fri, 27 Nov 2015 12:12:54 GMT

Hiya

I cant get the

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

please help

Re: Trend Line only appears when filter selected

reddy-s — Fri, 27 Nov 2015 12:31:20 GMT

Hi Joanna,

Simple use this instead of the Let statement:

measures:

LOAD * INLINE [

Re: Trend Line only appears when filter selected

joeybird — Fri, 27 Nov 2015 13:17:00 GMT

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

Re: Trend Line only appears when filter selected

reddy-s — Fri, 27 Nov 2015 13:27:57 GMT

Hi Joanna,

Is it possible to attach the sample file?

Re: Trend Line only appears when filter selected

JonnyPoole — Fri, 27 Nov 2015 13:28:06 GMT

‌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

Re: Trend Line only appears when filter selected

joeybird — Fri, 27 Nov 2015 14:19:17 GMT

Hiya

I have this working, but it wont show the 3rd line

when required

please help

Re: Trend Line only appears when filter selected

JonnyPoole — Fri, 27 Nov 2015 15:25:42 GMT

what is the current expression condition ? paste it in your reply

Re: Trend Line only appears when filter selected

joeybird — Fri, 27 Nov 2015 15:41:09 GMT

if( Valuelist('SaleTotal','DeptTotal') = 'SaleTotal', sum({$<[Department] =>} Sales),

if( Valuelist('SaleTotal','DeptTotal') = 'DeptTotal',

if( getselectedcount([Department]) = 'Catering' , sum({$<[Department] ="Catering">} Sales),

          if( getselectedcount([Department]) = 'HomeWare' , sum({$<[Department] ="HomeWare">} Sales) )

)

please help

Re: Trend Line only appears when filter selected

reddy-s — Fri, 27 Nov 2015 16:25:25 GMT

Hi Joanna,

The expression which you have used is wrong, instead try this:

if( Valuelist('SaleTotal','DeptTotal') = 'SaleTotal', sum({$<[Department] =>} Sales),

if( Valuelist('SaleTotal','DeptTotal') = 'DeptTotal',

if( Valuelist( GetFieldSelections([Department]) ) = 'Catering' , sum({$<[Department] ="Catering">} Sales),

          if( Valuelist( GetFieldSelections([Department]) ) = 'HomeWare' , sum({$<[Department] ="HomeWare">} Sales) )

)

Re: Trend Line only appears when filter selected

joeybird — Mon, 30 Nov 2015 09:31:05 GMT

Hiya

this sort of works, but I still cant get the third line to appear when the second department is selected

but I did amend

if( Valuelist('SaleTotal','DeptCatTotal', 'DeptHomewTotal')= 'SaleTotal', sum({$<[Department] =>} Sales),

if( Valuelist('SaleTotal','DeptCatTotal', 'DeptHomewTotal') = 'DeptTotal',

if( Valuelist( GetFieldSelections([Department]) ) = 'Catering' , sum({$<[Department] ="Catering">} Sales),

          if( Valuelist( GetFieldSelections([Department]) ) = 'HomeWare' , sum({$<[Department] ="HomeWare">} Sales) )

)

I have tried to create a value list of

Valuelist('SaleTotal','DeptCatTotal', 'DeptHomewTotal')

a third line in the key has now appeared ,

it still does not work

please help

Re: Trend Line only appears when filter selected

JonnyPoole — Mon, 30 Nov 2015 15:10:02 GMT

I think i understand. Below instead of department i am doing this on countries_world.Name.

The way the logic goes is:

if a country is selected, than show 3 lines for each valuelist element. The selected sales per month "sum(Sales)", the total selected sales for all months "sum ( total Sales)" , and the application total for all months and all countries "sum( {<countries_world.Name=>} total Sales)".

if no country is selected, then just show the monthly sales for the current selections.

if( GetSelectedCount(countries_world.Name)>0,

if( ValueList('MonthlySelectedSales','TotalSelectedSales','TotalSales')= 'TotalSelectedSales', sum ( total Sales),

if( ValueList('MonthlySelectedSales','TotalSelectedSales','TotalSales')= 'TotalSales', sum( {<countries_world.Name=>} total Sales),

if( ValueList('MonthlySelectedSales','TotalSelectedSales','TotalSales')='MonthlySelectedSales', sum(Sales)

)

sum( Sales))