Calculate number of competitors cheaper than me

[rasmusnielsen]

Hey guys,

I am trying to count the number of competitors which are cheaper than me. Currently I just have a table with a YearMonth dimension and the following measure:

Count({<[Competitor ID] = {"=avg([My Discount] - [Competitor Discount]) < 0"}>} distinct [Competitor ID] )

I got the measure from this post:

count sum in set analysis

My problem is that it seems like the resulting table is disregarding the YearMonth dimension. It is only showing the correct number when I select a specific YearMonth (such as 2018-jan).

Any suggestions?

[kfoudhaily]

I havn't really understood your need but you add this:

Count({$<

     YearMonth=,

     [Competitor ID] = {"=avg([My Discount] - [Competitor Discount]) < 0"}

     >}

     distinct [Competitor ID]

     )

[ali_hijazi]

where are you using this expression? on a KPI? then you need to specify the month year you want to view or you can

add to your set analysis ,MonthYear = {$(=max(monthYear))}

or if you are using the expression on a chart then
you can add the monthYear dimension to your chart and thus you can see the count of competitors per monthyear

[rasmusnielsen]

I have added a few screenshots to aid. Its a table in Qlik Sense sheet and I have added YearMonth as the dimension and the mentioned expression has been added (name of column is "Cheaper").

The first screenshot shows the data when there is no selection to Date.YearMonth. Cheaper = 3 for all months. If you look in the table under that, you can see all values for april. From that, I only have two values that are less than 0 and that is the expected count in my Cheaper column for April.

In the next screenshot I have select April. Now the "Cheaper" count is correct.

So, as initially stated it looks like my table is ignoring the dimension value and somehow my expression is incorrect:

Cheaper:

Count({<[Competitor ID] = {"=avg([My Discount] - [Competitor Discount]) < 0"}>} distinct [Competitor ID] )

I have also attached a test app that shows the same problem. Here the dimension is the date.

[rasmusnielsen]

Thanks for your answer. However, the suggested solution does not make any difference to the result.

[ogautier62 · Accepted solution]

Hi,

I think set analysis evaluate expression first, and so not take account dimension

for that try this :

sum(if(avg(total <CompetitorID> [My Discount] - [Competitor Discount]) < 0,1))

regards

[rasmusnielsen]

Thanks for your answer. I'm not completely sure on what you are trying to do. Is it possible that you can rewrite the original expression?

I guess you are right about that the dimension is not taken into account. If I calculate the difference between the discounts, then I have no problem getting it to work with the following measure:

Count({<[diff] = {"<0"}>} distinct competitor)

Where diff is calculated in the load script as

Load *,

     [My Discount] - [Competitor Discount] as diff;

Resident Data;

[ogautier62]

OK Rasmus

It works because all diff are same sign in your sample

Take care and try with a diff<0 and a diff >0 where avg>0

You'll count for 1 but you shoulf'nt

[rasmusnielsen]

Thank you for your response.

I updated my example and got this:

So, I added the diff column and the bottom table, I added the

Count({<[diff] = {"<0"}>} distinct competitor)

To me, it looks like it is working, but I am not completely sure. Are you suggesting that data can be setup to make this example fail?

Appreciate your feedback

[ogautier62]

OK,

you are wright if you have one only row for each month and competitor,

if so you don't need average

maybe I have missunderstood your data model