3 Replies Latest reply on Mar 28, 2017 2:28 AM by Okechukwu Ossai

    Current month and previous 6 month line chart evolution chart

    Ana RIta Oliveira

      Hi,

       

      I'm trying to create a dashboard where I want to show the last month value, the current value and the variation of a variable and the evolution of the last six months of the mean.

       

      I have created two different charts.

      The first one shows the value of the last, current value and variation of the variable.

       

                                   Last Month          Variation               Current month

      The second shows the evolution of the last six months.

       

      I need to have those two charts connected, so if the user chooses July in the first one is presented the values for June and July and in the second it presents the last 6 months (from February to July). To do this I use a parameter that gets the month to analyze and present the all the data.

       

      My problem appears when I choose for example January and I need to present the last six months. My evolution chart only shows the current month. In the first one I can get this change correctly.

       

      Someone can help me trying to figure it out how to get the change of the year?

       

      Attached you can find the first, second chart and the dashboard that combines the two with the parameter.

       

      Thank you all.

      Best,

      Rita

        • 1. Re: Current month and previous 6 month line chart evolution chart
          Okechukwu Ossai

          Hi Ana,

           

          Could you check the attached workbook to see if this is the solution you are looking for? I've made 2 changes to your workbook.

           

          1. MONTH(Mes Analise) which is on the column shelf should be continuous. This will ensure correct date sequence is maintained when "Last 6 months" data extend across 2 years.

           

          2. Your [Date Range] calculated field is not working correctly.

          For example, [Media Movel Clientes Ativados Corrente] is null for February 2016. However, your Last 6 months data when 07/2016 is selected includes data for Feb 2016. This is indeed Feb 2017 data. See the charts below.

           

           

          This becomes clearer when I break it up into years.

           

          You can see that your Last 6 months data (starting from July 2016) includes data from the future (2017) which is not possible.

           

           

          Solution

           

          I changed [Date Range] calculation to;

          [Mes Analise] >= DATEADD('month', -5, DATETRUNC('month', [Date])) AND [Mes Analise] <= DATETRUNC('month', [Date])

           

          Put this on the filter shelf and set to "True". When 07/2016 is selected, you now have the correct Last 6 Months data. February is missing because it contains null data.

           

           

           

          Last 6 months with January 2017 as reference month is working also.

           

           

          Let me know if this helps.

           

          Ossai

          2 of 2 people found this helpful
          • 2. Re: Current month and previous 6 month line chart evolution chart
            Ana RIta Oliveira

            Hi Ossai,

             

            Thank you a lot for your help. Indeed solved my problem. I forgot completly the year issue.

             

            Once again thank you for your time.

             

            Best,

            Rita

            1 of 1 people found this helpful
            • 3. Re: Current month and previous 6 month line chart evolution chart
              Okechukwu Ossai

              You're welcome Rita. Glad it helped.

               

              Ossai

              1 of 1 people found this helpful