Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rasmusnielsen
New Contributor III

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
MVP
MVP

Re: Comparing data for two different dates with various comparison methods

Try this

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

3 Replies
rasmusnielsen
New Contributor III

Re: Comparing data for two different dates with various comparison methods

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.

MVP
MVP

Re: Comparing data for two different dates with various comparison methods

Try this

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

rasmusnielsen
New Contributor III

Re: Comparing data for two different dates with various comparison methods

Yes! Thank you very much.

That was quick