Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
rasmusnielsen
Partner - Creator
Partner - Creator

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

Hi guys,

 My data model looks like this: 

Screenshot 2022-01-17 at 10.55.38.png

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:

store1.png

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?

 

Labels (2)
6 Replies
rasmusnielsen
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

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

Screenshot 2022-01-25 at 13.47.17.png

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

Screenshot 2022-01-25 at 13.51.48.png

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
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

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)?

Screenshot 2022-02-04 at 16.55.24.png