2 Replies Latest reply on Mar 21, 2012 5:57 PM by Matthew Fleck

    Question on min and max values

    Matthew Fleck

      Hello,

       

      It seems this should be a fairly common situation but I cannot find any posts with this exact issue.

       

      What I want to do is to show purchase history over time and the changes in price individual customers have paid.

       

      Some customers will have paid the same price either because they only made one purchase or they paid the same price every time they bought the same product. In the end I want to filter these customers and purchases out and only show those where the same customer has bought the same product but at different prices over the duration of this view.

       

      I thought be best way to do this would be to get the min and max prices paid by that customer for that product regardless of purchase date and filter our where the difference between the two is zero. however, as i am showing the purchase history over time, the min and max calculations are being determine using the transaction date.

       

      I have tried using table calculations but cannot see how to have them solve this problem. The end result, once this worksheet is filtered, is for there to be three lines on the chart. The only lines should be for Bob, Sam and John.

       

      thank you everyone,

      Matt

        • 1. Re: Question on min and max values
          Tracy Rodgers

          Hi Matthew,

           

          By creating a calculated field similar to the following and placing it on the filter shelf will allow you to get the desired outcome:

           

          if window_min(sum(price))=window_max(sum(price)) then 'filter out' else 'keep' end

           

          I hope this helps!

           

          -Tracy

          1 of 1 people found this helpful
          • 2. Re: Question on min and max values
            Matthew Fleck

            Hi Tracy,

             

            Thank you, that was very helpful but it was not exactly the solution to my problem. The real worksheet has a lot of complex data which I thought I was able to simplify correctly in my sample but I missed something. It could be that the same customer can and often does by different products and different prices.

             

            When I applied your formula as a filter I still had a lot of data points where the price never changed during the time period but there were different prices for the same customer buying different products. I am not concerned about the dashboard showing these values, only when the price is different for the same customer and product.

             

            When I changed your solution to compare the window_min(ATTR(price)) to the window max it looks like it solves my problem but I have more research to do tomorrow to see if only the correct data points are being filtered.

             

            Thank you again,

            Matt