15 Replies Latest reply: Nov 30, 2015 10:10 AM by Jonathan Poole RSS

    Trend Line only appears when filter selected

    Joanna Seldon

      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
          Jonathan Poole

          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
              Joanna Seldon

              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
                  Sangram Reddy

                  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
                    Jonathan Poole

                    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
                      Jonathan Poole

                      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))

                  • Re: Trend Line only appears when filter selected
                    Sangram Reddy

                    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
                        Joanna Seldon

                        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
                            Sangram Reddy

                            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))

                                 )

                            )

                              • Re: Trend Line only appears when filter selected
                                Joanna Seldon

                                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
                                    Sangram Reddy

                                    Hi Joanna,

                                     

                                    Is it possible to attach the sample file?

                                      • Re: Trend Line only appears when filter selected
                                        Joanna Seldon

                                        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
                                            Sangram Reddy

                                            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
                                                Joanna Seldon

                                                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