16 Replies Latest reply: Jul 5, 2018 8:38 AM by Mikel De RSS

    How to show all months in a line chart

    Mikel De

      Hello!

       

      I have two tables - one with KPI data and another one with invoice data. The tables are linked on Material and Month. The KPI table has data for each month while the invoice data is missing for some months. What I want is a line chart with KPI and KPI_Month as dimensions and Max(KPI_Value) as measure. After selecting a material, the chart should display the associated KPI for all months and not only for the months with invoice data. Please see the gif below and notice how some months disappear after a material gets selected:

       

      Example.gif

       

      Here is the sample data:

       

      KPI:
      LOAD
      KPI,
      KPI_Value,
      DATE(DATE#(KPI_Month, 'MM.YYYY')) AS KPI_Month,
      // Material
      Material & '-' & DATE(DATE#(KPI_Month, 'MM.YYYY')) AS Material_Month_Key
      INLINE [
      KPI,KPI_Value,KPI_Month,Material
      Target_LBSD,101,07.2016,5000
      Target_LBSD,102,08.2016,5000
      Target_LBSD,103,09.2016,5000
      Target_LBSD,104,10.2016,5000
      Target_LBSD,102,11.2016,5000
      Target_LBSD,104,12.2016,5000
      Target_LBSD,103,01.2017,5000
      Target_LBSD,102,02.2017,5000
      Target_LBSD,101,03.2017,5000
      Target_LBSD,102,04.2017,5000
      Target_LBSD,103,05.2017,5000
      Target_LBSD,104,06.2017,5000
      Target_EGDA,106,07.2016,6000
      Target_EGDA,107,08.2016,6000
      Target_EGDA,108,09.2016,6000
      Target_EGDA,106,10.2016,6000
      Target_EGDA,108,11.2016,6000
      Target_EGDA,108,12.2016,6000
      Target_EGDA,109,01.2017,6000
      Target_EGDA,107,02.2017,6000
      Target_EGDA,108,03.2017,6000
      Target_EGDA,109,04.2017,6000
      Target_EGDA,120,05.2017,6000
      Target_EGDA,106,06.2017,6000
      ];
      
      
      Data:
      LOAD
      Material AS Material_ID,
      Material_Name,
      DATE(DATE#(Invoice_Month, 'MM.YYYY')) AS Invoice_Month,
      Invoice_Value,
      Invoice_Quantity,
      Material & '-' & DATE(DATE#(Invoice_Month, 'MM.YYYY')) AS Material_Month_Key
      INLINE [
      Material,Material_Name,Invoice_Month,Invoice_Value,Invoice_Quantity
      5000,LBSD,07.2016,150,2500
      5000,LBSD,09.2016,140,2500
      5000,LBSD,11.2016,130,2500
      5000,LBSD,01.2017,145,2500
      5000,LBSD,03.2017,155,2500
      5000,LBSD,05.2017,150,2500
      6000,EGDA,07.2016,160,2900
      6000,EGDA,08.2016,180,2900
      6000,EGDA,12.2016,150,2900
      6000,EGDA,01.2017,155,2900
      6000,EGDA,04.2017,165,2900
      6000,EGDA,05.2017,170,2900
      ];
      
      
      

       

       

      This should not be too complicated but I was not able to figure it out. I hope somebody has an idea.

       

      Thank you!

        • Re: How to show all months in a line chart
          Ujjwal Raja

          Hi,

           

          You can exclude the effect of the selection of Material_Name with set analysis by updating the measure in line chart with Max({1<Material_Name = >}KPI_Value).

           

          Let me know if it helps

            • Re: How to show all months in a line chart
              Mikel De

              Thanks, I thought about that, but then I lose the association Material to KPI_Value, i.e. when I select a material, all KPI values are shown on the chart:

               

              Capture.PNG

               

              What I want is to select, e.g. LBSD and the chart to show me all values for Target_LBSD only.

                • Re: How to show all months in a line chart
                  Ujjwal Raja

                  Hi,

                   

                  Mark the continuous scale in Xaxis in Appearance setting of the chart.

                  Please look at the image below.

                   

                    image 1.PNG

                    • Re: How to show all months in a line chart
                      Mikel De

                      The outcome is the same as before.

                        • Re: How to show all months in a line chart
                          Ujjwal Raja

                          I am using the data provided by you and for the selection which you made it is showing me like this.

                           

                          image 12.PNG

                            • Re: How to show all months in a line chart
                              Mikel De

                              Yes, you get exactly the same result as me. Please compare to the gif in the first post. After a material is selected you end up with a graph showing only six values:

                              07.2016

                              09.2016

                              11.2016

                              01.2017

                              03.2017

                              05.2017


                              The data for the other six months is missing.

                               

                                • Re: How to show all months in a line chart
                                  Ujjwal Raja

                                  Hi Mikel,

                                   

                                  The association was not correct.

                                  I modified the code.

                                   

                                  KPI: 

                                  LOAD  

                                  KPI, 

                                  KPI_Value, 

                                  DATE(DATE#(KPI_Month, 'MM.YYYY')) AS KPI_Month, 

                                  Material  as Material_ID

                                  //Material & '-' & DATE(DATE#(KPI_Month, 'MM.YYYY')) AS Material_Month_Key 

                                  INLINE [ 

                                  KPI,KPI_Value,KPI_Month,Material 

                                  Target_LBSD,101,07.2016,5000 

                                  Target_LBSD,102,08.2016,5000 

                                  Target_LBSD,103,09.2016,5000 

                                  Target_LBSD,104,10.2016,5000 

                                  Target_LBSD,102,11.2016,5000 

                                  Target_LBSD,104,12.2016,5000 

                                  Target_LBSD,103,01.2017,5000 

                                  Target_LBSD,102,02.2017,5000 

                                  Target_LBSD,101,03.2017,5000 

                                  Target_LBSD,102,04.2017,5000 

                                  Target_LBSD,103,05.2017,5000 

                                  Target_LBSD,104,06.2017,5000 

                                  Target_EGDA,106,07.2016,6000 

                                  Target_EGDA,107,08.2016,6000 

                                  Target_EGDA,108,09.2016,6000 

                                  Target_EGDA,106,10.2016,6000 

                                  Target_EGDA,108,11.2016,6000 

                                  Target_EGDA,108,12.2016,6000 

                                  Target_EGDA,109,01.2017,6000 

                                  Target_EGDA,107,02.2017,6000 

                                  Target_EGDA,108,03.2017,6000 

                                  Target_EGDA,109,04.2017,6000 

                                  Target_EGDA,120,05.2017,6000 

                                  Target_EGDA,106,06.2017,6000 

                                  ]; 

                                   

                                   

                                  Data:

                                   

                                   

                                  LOAD  

                                  Material AS Material_ID, 

                                  Material_Name, 

                                   

                                   

                                  DATE(DATE#(Invoice_Month, 'MM.YYYY')) AS Invoice_Month, 

                                  Invoice_Value, 

                                  Invoice_Quantity

                                  //Material & '-' & DATE(DATE#(Invoice_Month, 'MM.YYYY')) AS Material_Month_Key

                                   

                                   

                                  INLINE [ 

                                  Material,Material_Name,Invoice_Month,Invoice_Value,Invoice_Quantity 

                                  5000,LBSD,07.2016,150,2500 

                                  5000,LBSD,09.2016,140,2500 

                                  5000,LBSD,11.2016,130,2500 

                                  5000,LBSD,01.2017,145,2500 

                                  5000,LBSD,03.2017,155,2500 

                                  5000,LBSD,05.2017,150,2500 

                                  6000,EGDA,07.2016,160,2900 

                                  6000,EGDA,08.2016,180,2900 

                                  6000,EGDA,12.2016,150,2900 

                                  6000,EGDA,01.2017,155,2900 

                                  6000,EGDA,04.2017,165,2900 

                                  6000,EGDA,05.2017,170,2900 

                                  ]; 

                                   

                                   

                                   

                                   

                                  Now , look at the output

                                  img 212.PNG

                                    • Re: How to show all months in a line chart
                                      Mikel De

                                      Hi,

                                       

                                      Thank you very much, I appreciate your time! Is there any way to do this in the front end? I would prefer not to change the script because:

                                      1) this is not my app and it will take time to implement the change

                                      2) the data model is much larger than 2 tables and changing this will make other amendments necessary

                                       

                                      Thanks!

                                        • Re: How to show all months in a line chart
                                          Ujjwal Raja

                                          Association in your script is based on the Material Month Key which on KPI table has Kpi_month and on the Data table has Invoice Month.

                                           

                                          As far as I can think if the data model is not correct then bypassing it and showing the different result in the front end is difficult. Either you have to correct the association or add a master calendar in your script so that KPIs pertaining to every date is possible or you can try a data island for your desired date.

                                           

                                          I tried 'calculated dimension' using valueloop() function but it did not give the correct result.

                                            • Re: How to show all months in a line chart
                                              Mikel De

                                              I see what you mean, there is no Material_Month_Key value associated to the materials when an Invoice_Month is missing. This screws up my graph after a selection has been made.

                                               

                                              I was hoping that there is anything possible on the front end, e.g. some kind of set analysis that will "fix" the dimension of the line chart. Thank you for your help, I will keep trying without changing the script for the moment.

                            • Re: How to show all months in a line chart
                              Niclas Anderström

                              Hi,

                               

                              Try adding an expression:

                               

                              only({1}Month)

                               

                              That does not have any visualization (will just be calculated but not shown). This ensure that all months are always shown.

                               

                              If you want to show all months for which there is data you can do it a bit smarter;

                               

                              only({1<Month = {">=$(=Min(Month))<=$(=Max(Month))"}>}Month)

                              • Re: How to show all months in a line chart
                                Olivier GAUTIER

                                Hi,

                                 

                                maybe this option in graph :

                                 

                                • Re: How to show all months in a line chart
                                  Olivier GAUTIER

                                  Hi

                                   

                                  you use kpi_Month as dimension,

                                  if month not exist you can't display it

                                   

                                   

                                  use Month of your calendar as dimension

                                   

                                  regards