Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rasmusnielsen
Partner - Creator
Partner - Creator

Comparing data for two different dates with various comparison methods

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

1 Solution

Accepted Solutions
sunny_talwar

Try this

Count(DISTINCT Aggr(If(Avg({$<date = {"$(vDate1)"}>} discount) > Avg({$<date = {"$(vDate2)"}>} discount), competitor), brand, competitor))

View solution in original post

3 Replies
rasmusnielsen
Partner - Creator
Partner - Creator
Author

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.

sunny_talwar

Try this

Count(DISTINCT Aggr(If(Avg({$<date = {"$(vDate1)"}>} discount) > Avg({$<date = {"$(vDate2)"}>} discount), competitor), brand, competitor))

rasmusnielsen
Partner - Creator
Partner - Creator
Author

Yes! Thank you very much.

That was quick