8 Replies Latest reply: Apr 12, 2017 12:57 PM by Mandi Hu RSS

    Display decreasing trend of data in bar chart

    Mandi Hu

      Hi,

       

      I have a set of data which has below columns

      ItemInventoryDaily ConsumptionDays of Inventory = Inventory / Daily Consumption
      11000040025
      22000080025
      31500050030

       

       

      Can I display a bar chart which shows the decreasing trend of items? Take item 1 for example.

      DayInventory
      19600 (10000-400)
      29200
      38800
      ...
      250

       

       

      If one item is selected, the bar chart shows the decreasing trend of this item. If no item is selected, the bar charts shows the decreasing trend of total inventory.

       

      Thank you in advance!

      Mandi

        • Re: Display decreasing trend of data in bar chart
          Nicole Smith

          Something like this should work:

           

          Dimension (you may need to adjust the second argument depending on the amount of data you have--I just set it to 100 for this example):

          =ValueLoop(1, 100, 1)

           

          Expression (you'll need to adjust the second argument to be the same as in the dimension--just like I have 100 here):

          if(

            sum(Inventory)-sum([Daily Consumption])*ValueLoop(1, 100, 1) >= 0,

            sum(Inventory)-sum([Daily Consumption])*ValueLoop(1, 100, 1)

          )

           

          I've also attached an example file.

            • Re: Display decreasing trend of data in bar chart
              Mandi Hu

              Hi Nicole,

               

              Thanks for your help. Is it possible to start the valueloop from a date? I mean for each bar, the label is date instead of 1,2,3,...100.

               

              Thanks,

              Mandi

                • Re: Display decreasing trend of data in bar chart
                  Nicole Smith

                  Okay, so for using dates instead of integers...

                   

                  I added this to the load script to create a date/calendar table:

                   

                  SET vStartDate = MakeDate(2017, 1, 1);
                  SET vNumDays = 100;
                  
                  Dates:
                  LOAD date($(vStartDate) + IterNo() - 1) AS [Date],
                    IterNo() AS [Day Number]
                  AutoGenerate 1
                  WHILE IterNo() <= $(vNumDays);
                  

                   

                  You can adjust the start date and the number of days to whatever you need them to be (I used 100 since that is what I had used previously in the value loop).

                   

                  Then I updated the table to have:

                   

                  Dimension:

                  Date

                   

                  Expression:

                  if(

                    sum(Inventory)-sum([Daily Consumption])*[Day Number] >= 0,

                    sum(Inventory)-sum([Daily Consumption])*[Day Number]

                  )

                   

                  I've attached an updated example file as well.

                    • Re: Display decreasing trend of data in bar chart
                      Mandi Hu

                      Hi Nicole,

                      Thank for the reply. In the solution you provided, is it possible to set vStartDate varied as the largest date among date selections users select in Dashboard?

                       

                       

                      Thank you very much,

                      Mandi

                        • Re: Display decreasing trend of data in bar chart
                          Nicole Smith

                          So I didn't do it exactly the way you asked, but I think the functionality is the same...

                           

                          I took [Day Number] out of the load script to implement it dynamically, so the load script piece that I added previously now looks like this:

                          SET vStartDate = MakeDate(2017, 1, 1);
                          SET vNumDays = 100;
                          
                          Dates:
                          LOAD date($(vStartDate) + IterNo() - 1) AS [Date]
                          AutoGenerate 1
                          WHILE IterNo() <= $(vNumDays);
                          
                          
                          
                          

                           

                          You need to set the vStartDate and vNumDays to cover the full range of days that you want available to the user to select.  You can make this range as large or small as you'd like.

                           

                          Then, on the dashboard itself, I added a new variable vChartStartDate, which I added a calendar object that the user can select dates from.  I set the Min Value to =min(Date) and the Max Value to =max(Date).

                           

                          Dimension:

                          =if(Date >= vChartStartDate, Date)

                           

                          Expression:

                          if(

                            sum(Inventory)-sum([Daily Consumption])*RowNo() >= 0,

                            sum(Inventory)-sum([Daily Consumption])*RowNo()

                          )

                           

                          I've attached the updated example file.  You can see that as you select new dates in the vChartStartDate calendar object, it changes the first date on the charts.