3 Replies Latest reply: Jun 19, 2018 11:01 AM by Rasmus Nielsen RSS

    Comparing data for two different dates with various comparison methods

    Rasmus Nielsen

      Hi guys,

       

      I'm having some problems understanding how to compare data for two different dates.

       

      My data model looks like:

      Data:

      Load * Inline [

        date, brand, competitor, discount

          2018-06-01, 'B1', 'C1', 0.11

          2018-06-02, 'B1', 'C1', 0.12

          2018-06-03, 'B1', 'C1', 0.13

          2018-06-04, 'B1', 'C1', 0.14

          2018-06-01, 'B1', 'C2', 0.17

          2018-06-02, 'B1', 'C2', 0.16

          2018-06-03, 'B1', 'C2', 0.15

          2018-06-04, 'B1', 'C2', 0.13

          2018-06-01, 'B1', 'C3', 0.27

          2018-06-02, 'B1', 'C3', 0.26

          2018-06-03, 'B1', 'C3', 0.25

          2018-06-04, 'B1', 'C3', 0.23

      ]

       

      My sheet contains three tables. The first one is the raw data. The second one shows the data based on the Brand dimension. The third table shows the data for both brand and competitor dimensions. In my sheet, I can set two date variables with different date values. E.g. vDate1 = 2018-06-02 and vDate2 = 2018-06-03. (I'm using the SimpleFieldSelect extension).

      Screen Shot 2018-06-19 at 16.28.34.png

      The data represents a bunch of competitors with brand B1 and with various discounts on different days. If you look at the attached image, I have a table in the middle which shows the brand dimension and it shows the avg. discounts for all the competitors for the two selected dates.

      The column UP is supposed to count how many competitors which have lowered their discount from vDate1 to vDate2. Looking at the bottom table, I can see that there are two competitors (C3 and C2) that have lowered the discount and thus the column UP should read 2.


      I am struggling to find a correct expression for this. I have tried with the aggr function, but I am not getting the expected result.


      The values in T1 are calculated using:

      Avg({$<date = {"$(vDate1)"}>} discount)


      We are going to add other comparisons as well (such as how many competitors did not change discount, how many increased discount etc.), but I guess they all should use the same approach.

       

      Appreciate any suggestions