Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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).
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
Try this
Count(DISTINCT Aggr(If(Avg({$<date = {"$(vDate1)"}>} discount) > Avg({$<date = {"$(vDate2)"}>} discount), competitor), brand, competitor))
The UP column in the screenshot is currently just showing the Count(discount) which is 12, which is the number of rows in the raw data.
Try this
Count(DISTINCT Aggr(If(Avg({$<date = {"$(vDate1)"}>} discount) > Avg({$<date = {"$(vDate2)"}>} discount), competitor), brand, competitor))
Yes! Thank you very much.
That was quick