Find All Values for Group Where Column Value Matches Column Value of Parameter Minimum

bretg — Tue, 26 Apr 2022 12:23:27 GMT

Hi everyone,

I just started using Qlik Sense (second day - building off of another person's work), and I'm looking for help writing an expression to solve the following problem.

So let's say I've collected the following data on sales figures by product at multiple locations for a few months now:

Full dataset

My goal is to find the month with the lowest total sales figures for each location, along with the sales figures for each product that correspond to that month. I'm hoping to determine which product we were focusing on when we had lower sales, so we can pivot to targeting sales of a different product.

This is my desired result:

Desired result

Currently, I've created a table object in my sheet, where I'm running the following expression under the "Measures" section in an attempt to get that table:

Min({1<Year = {"2022"}>} Sales)

I've chosen "Location" as my row dimension, and "Product" as my column dimension. However, while I get the right rows/columns, I get the wrong values:

Current result

As you can see, it's pulling the minimum value observed for each column, so the product values don't add up to the total value. What I want it to do is find the month with the lowest total, then pull the amounts for the other products at that location for that same month.

Please let me know how I can solve this problem! I know there are "Aggr" and "Match" functions in Qlik, but I've never worked with them before, and don't know if they apply to the problem at hand. Thank you in advance for the help!

topic Find All Values for Group Where Column Value Matches Column Value of Parameter Minimum in App Development

Find All Values for Group Where Column Value Matches Column Value of Parameter Minimum