3 Replies Latest reply: Dec 9, 2015 6:23 AM by Sangram Reddy RSS

    Gain and Loss

    KENNETH ANDERSEN

      I need to make an expression to measure ‘Gain and Loss’.

       

      If you look at this data example, I like eg. to show:

      Udklip.JPG

       

      LOSS:

      I like to show how many pieces of any product has been sold by all those who bought a certain product in a certain year but not the following year.

       

      Let’s look at an example:

       

      Using my filter in the dashboard I could chose Year = 2015 and Product = A. Then I like to show/measure how many pieces of any product has been sold BUT ONLY for Ids who bought Product A in 2014 AND NOT in 2015.

       

      Looking into data I can see that ID 1 bought Product A in 2014 but not in 2015 (product C and B should appear then appear in my bar chart).

       

      ID 2 has bought Product in 2014 and 2015 as well and thereby shouldn’t appear.

      ID 3 bought Product A in 2014 but not in 2015 (product C should appear then appear in my bar chart).

      The result will be a bar graph with the following number:

      Product C = 17

      Product B = 6

        • Re: Gain and Loss
          Sangram Reddy

          Hi Kenneth,

           

          This should help you get the answer:

           

          I have used the sample data you have given and built a bar chart:

          Capture.PNG

          Capture1.PNG

           

          Make use of this as your measure expression : Sum({$< Product = e({< Product = {$(=getfieldselections(Product))} >}Product), Year = e({< Year = {$(=getfieldselections(Year))} >}Year) >}Quantity)

           

          - Sangram Reddy

            • Re: Gain and Loss
              KENNETH ANDERSEN

              Hi Sangram,

               

              Thanks for your reply. It looks good choosing product A. But somehow chossing another product I don't get the result I like. Ive added antoehr line to the data file to check product B:

               

              Udklip.JPG

               

              I can see that ID 3 is the only one who has bought product B i 2014 but not in 2015. Therefore the bar chart should only show Product C = 7.

               

              The trick should be first to pick out the IDs  who has bough the product in 2014 and not in 2015 and the then bar chart should display the product bought in 2015.