Count number of data points for dimension based on a subset of data

[rasmusnielsen]

Hi guys,

 My data model looks like this: 

We have a bunch of stores with competitors of various brands. For each competitor, we know the physical distance to the store and for each competitor's price, we know the delta to the price of the store. 

I'm attempting to create a heatmap where the dimensions are the delta values and the brands (sorted by the distance to the nearest competitors). The measure is the count of competitor prices that match the delta dimension. But, we only want to consider prices for the nearest competitor of each brand.

That is:

For each store, we need to determine the nearest competitor of each brand. Let's call that set CLOSEST_COMPETITORS.

Only the prices of competitors in the set CLOSEST_COMPETITORS should be considered in the heatmap.

The brand dimension of the heatmap is sorted by the distance of the competitors in CLOSEST_COMPETITORS. If multiple stores are selected, then it should be the AVG distance of the nearest competitors per brand.

The range of values in the delta dimension is adjustable by the user. The default range will be between -10 and 10.

I have attached a test app that demonstrates the data model and a couple of screenshots that displays the expected output.

Example when store1 is selected:

Competitors A, D, and E are the closest one of each brand, to the store, so we will only consider the prices of these competitors. The heatmap will show the count of each delta.

I'm struggling to create a measure that filters out the prices that I don't care about. I think I can find the competitors for a single store with:

Aggr(FirstSortedValue(competitor, distance), brand)

 But this does not work when multiple stores are selected and I need an avg of each of the closest competitors per store per brand.

I imagine that the expression above should be used in a set expression in my count measure of the heatmap, but I'm not completely sure how to achieve that.

Any suggestions?

[rasmusnielsen]

I just want to add that I expect to implement the brand sorting by having a custom sort expression on the brand dimension.

Also, the measure for the heatmap would probably be something like:

Count({$<competitor={"???"}>} timestamp)

[rasmusnielsen]

I have rewritten my data model in the load script so that each store is only linked to the nearest competitor per brand. This made the UI part a lot easier.

This also allows me to only load data for the relevant store/competitor pairs, instead of loading all data and then have filtering in the UI.

[rasmusnielsen]

Hmm, it turns out that I need the dynamic solution anyway. 

I can display the average distance of all the closest competitors to each store per brand with:

Avg(Aggr(FirstSortedValue({<[price]={"*"}>}[distance], distance), [brand], [store]))

Now I am attempting to create a straight table with just brand, delta, and number of prices for that delta.

[rasmusnielsen]

I'm trying to simplify the model. Given this data:

I want to create a straight table with the brand dimension. If I select store 1, I expect the table to look like:

That is, count of all prices for the closest competitor of each brand.

For store1 and brand ABC, competitorA is closest and it has 3 prices.

For store1 and brand D, competitorD is closest and it has 3 prices.

For store1 and brand EF, competitorE is closed and it has 1 price.

[rasmusnielsen]

Should I count the prices and then use a set expression to limit the data set to nearest competitors only?

Something like:

Count({$<competitor={"$(=FirstSortedValue(competitor, distance))"}>}price)

But this does not work

[rasmusnielsen]

This is quite a head-scratcher to me. I was really hoping someone could point me in the right direction.

In this table, the two first columns are correct, but I can't figure out how to make the count expression in the 3rd column. The example below it is just counting all prices for the brand.

How can I make the 3rd column count only prices for the nearest competitor (the one displayed in the 2nd column)?