4 Replies Latest reply: Jun 8, 2012 3:16 PM by jagan mohan rao appala RSS

    Simple Expressions

      So, this is not really a question unless you wish to add to the thread.  I am marking it as a question so that I can give points to cool additions.

       

      I was finding that expression writing was not coming as naturally to me as I would like so I spent some time writing out a bunch of simple expressions that control whether a graph is:

      • Showing absolute values or relative values
      • Is showing the first value from a data set, the last value from a data set, or all data in the selected data set.
      • For fun, I added a little expression to show both the first data and the last data side by side.

       

       

      I could totally see adding another set of formulas that work for the ENTIRE data set regardless of the date selection.

       

      I am attaching a QVW file but I am also listing the text here for those that only have the free version.

       

      Enjoy.

       

      // ----------------------------------------------------------------------
      // | My data set has a DATE, STORE, INVENTORY
      // | The date is the date that the inventory level was captured
      // | The store is the store location of the inventory
      // | The inventory is the number of items counted in inventory
      // |
      // | The following attempt to look at all of the "simple" expressions
      // | that can be used to look at this data in a chart
      // |
      // ----------------------------------------------------------------------
      //
      // ----------------------------------------------------------------------
      // ABSOLUTE DATA values for EACH DATE within SELECTED DATE RANGE
      // ----------------------------------------------------------------------
      // SUM([Inventory])
      //
      // ----------------------------------------------------------------------
      // ABSOLUTE DATA values for LAST DATE within SELECTED DATE RANGE
      // ----------------------------------------------------------------------
      // SUM (if([Date]=max(total [Date]),[Inventory],0))
      //
      // -------------------------------------------------------------------------
      // ABSOLUTE DATA values for FIRST DATE within SELECTED DATE RANGE
      // -------------------------------------------------------------------------
      // SUM (if([Date]=min(total [Date]),[Inventory],0))
      //
      // ----------------------------------------------------------------------
      // RELATIVE DATA values for EACH DATE within SELECTED DATE RANGE
      // RELATIVE TO THE ENTIRE TIME PERIOD
      // Not really applicable to this story but since I did it, I thought
      // that I would keep it here.
      // ----------------------------------------------------------------------
      // SUM ([Inventory]) / SUM ( total [Inventory])
      //
      // ----------------------------------------------------------------------
      // RELATIVE DATA values for EACH DATE within SELECTED DATE RANGE
      // RELATIVE to EACH SPECIFIC DATE (normalizes ratios)
      // ----------------------------------------------------------------------
      // SUM ([Inventory]) / SUM ( total <[Date]> [Inventory])
      //
      // ----------------------------------------------------------------------
      // RELATIVE DATA values for LAST DATE within SELECTED DATE RANGE
      // ----------------------------------------------------------------------
      // SUM (if([Date]=max(total [Date]),[Inventory],0)) / SUM ( total <[Date]> if([Date]=max(total [Date]),[Inventory],0))
      //
      // -------------------------------------------------------------------------
      // RELATIVE DATA values for FIRST DATE within SELECTED DATE RANGE
      // -------------------------------------------------------------------------
      // SUM (if([Date]=min(total [Date]),[Inventory],0)) / SUM ( total <[Date]> if([Date]=min(total [Date]),[Inventory],0))
      //
      // ----------------------------------------------------------------------
      // ** ** ** ** ** Now some really fun stuff ** ** ** ** **
      // ----------------------------------------------------------------------
      //
      // ----------------------------------------------------------------------
      // ABSOLUTE DATA values for FIRST DATE within SELECTED DATE RANGE
      // COMPARED TO the LAST DATE within SELECTED DATE RANGE
      // ----------------------------------------------------------------------
      // SUM (if([Date]=min(total [Date]),[Inventory],if([Date]=max(total [Date]),[Inventory],0)))
      //
      // ----------------------------------------------------------------------
      // RELATIVE DATA values for FIRST DATE within SELECTED DATE RANGE
      // COMPARED TO the LAST DATE within SELECTED DATE RANGE
      // ----------------------------------------------------------------------
      // SUM (if([Date]=min(total [Date]),[Inventory],if([Date]=max(total [Date]),[Inventory],0))) / SUM ( total <[Date]> if([Date]=min(total [Date]),[Inventory],if([Date]=max(total [Date]),[Inventory],0)))
      //