Find the value before selected

[HeyAbbott]

Good evening,

I have a large table where a append a new report each month, and each month's report includes a REPORT_DATE (example: '1/4/2021' , '2/1/2021' , '3/2/2021', etc.) I'm trying to build a pivot table in my app where I show a single month's sales numbers then compare to the prior month's numbers. It looks something like this:

PRODUCT	MAX(REPORT_DATE,2) SALES $	MAX(REPORT_DATE) SALES $	Delta $	MAX(REPORT_DATE,2) SALES #	MAX(REPORT_DATE) SALES #	Delta #
SOCKS	$100	$50	$50	10	5	5
SHIRTS	$50	$40	$10	20	10	10
PANTS	$25	$50	-$25	5	6	-1

With the fake data, it doesn't make much sense, but it matches conceptually. So, I'm using set analysis and the MAX function to do my aggregation, and it works fine...until the user decides they want to look at a comparison from several months ago. So, if the user selects a REPORT_DATE of '5/1/2020', then MAX(REPORT_DATE) returns '5/1/2020', which is good. But now MAX(REPORT_DATE,2) returns '-' instead of '4/3/2020' because there is only one REPORT_DATE in the selected data. I need a set analysis that will find the REPORT_DATE before the selected value.

I've tried everything I can think of, and I can't come up with anything. Hoping the masses have a suggestion for attacking this problem.

Thanks, all!

[GaryGiles]

I'd have to see the expressions you are using to know for sure, but a couple of things you can try:

In you set analysis, change your set Identifier to 1.  The set Identifier is the charactor(s) between the '{' and '<' in your set analysis.  For example:

sum({$<Report_Date={$(vSelectedReportDate)}>} Sales)

Use this instead: 

sum({1<Report_Date={$(vSelectedReportDate)}>} Sales)

the '$' is your set Identifier.  If there is no character(s) between { and <, it defaults to $, which means that all filters are applied to your set.  A '1' tells Qlik to ignore all filters, so any Filter on Report_Date will be ignore.  But, so will other filters that you may want.

You can also try to add 'Report_Date=' to your set analysis to ignore any filter on Report_Date.

Again, without seeing your expressions, it hard to identify the needed approach.

[HeyAbbott]

@GaryGiles  Thanks for the reply!

I did try the {1} in the set analysis, but it did not accomplish the desired result. Here are the formulas I'm using:

For the Current REPORT_DATE: Sum({<REPORT_DATE={'$(=MAX(REPORT_DATE))'}>}SALES)

This syntax works fine, and as I make selections, it bases the MAX on the available dataset, so it continues to show the desired date.

For the Prior REPORT_DATE: Sum({<REPORT_DATE={'$(=MAX(REPORT_DATE,2))'}>}SALES)

As I said, if I select the REPORT_DATE from six months ago, I get '0.00' because the set analysis returns a NULL. That makes sense, since there is only one available REPORT_DATE so we can't see the second greatest.

I've tried using the '1' identifier a few different ways:

Sum({1<REPORT_DATE={'$(=MAX(REPORT_DATE,2))'}>}SALES)

I still get a '-' from my MAX function, so the identifier doesn't help here

Sum({<REPORT_DATE={'$(=MAX({1}REPORT_DATE,2))'}>}SALES)

I get a result from the MAX function now, but it returns the second greatest date from the entire dataset, regardless of my selected report date.

I know set analysis can be tricky to get the right syntax. Any other suggestions?

Thanks, again!

[HeyAbbott · Accepted solution]

I finally figured out a solution (it's not elegant, but it works). I created a variable to find the date of the Prior month's report; the variable's value is calculated as:

if(GetSelectedCount(REPORT_DATE)=1,
DATE(MIN(ALL REPORT_DATE,$(=FIELDINDEX('REPORT_DATE',GetFieldSelections(REPORT_DATE)))-1)),
MAX(REPORT_DATE,2))

Basically, if a REPORT_DATE is selected, it uses FieldIndex to get the next lesser value. I use that variable in a set analysis, and it works as needed.

Thanks for your help!