Compare this year vs Last Year sales (same week) only if we sold more than 4 last year

djbloiss — Tue, 23 Oct 2018 14:31:53 GMT

I am working on an expression that will show items that we sold last more than 4 cases, in the current week last year, and we sold less this year. When doing it as one expression, it looks like this:

if(sum({$<[CustItemKey]={"=sum({$<[FISC WeekId]={'=$(vMax_SO_Closed_Week_LY)'}>} [Actual Quantity])>4"}, [FISC WeekId]={'=$(vMax_SO_Closed_Week)'}>} [Actual Quantity])

sum({$<[CustItemKey]={"=sum({$<[FISC WeekId]={'=$(vMax_SO_Closed_Week_LY)'}>} [Actual Quantity])>4"}, [FISC WeekId]={'=$(vMax_SO_Closed_Week_LY)'}>} [Actual Quantity]),

sum({$<[FISC WeekId]={'=$(vMax_SO_Closed_Week_LY)'}>} [Actual Quantity]))

Unfortunately that is coming up blank. So I broke them down into separate expressions to see the results.

For This Year: sum({$<[CustItemKey]={"=sum({$<[FISC WeekId]={'=$(vMax_SO_Closed_Week_LY)'}>} [Actual Quantity])>4"}, [FISC WeekId]={'=$(vMax_SO_Closed_Week)'}>} [Actual Quantity])

For Last Year: sum({$<[CustItemKey]={"=sum({$<[FISC WeekId]={'=$(vMax_SO_Closed_Week_LY)'}>} [Actual Quantity])>4"}, [FISC WeekId]={'=$(vMax_SO_Closed_Week_LY)'}>} [Actual Quantity])

These expressions are giving me the same exact values. so they are definitely not working. There is no way we sold the same exact values for everyone last year and this year.

Here are some explanations:

[FISC WeekId] is a field with the week number in the format of YYYYWW

vMax_SO_Closed_Week is The Maximum Week this year with data

VMax_SO_Closed_Week_LY is Last year of the Maximum week#

CustItemKey is a concatanted field that gives us a combination of customer and item, so we can test specific customer/ item relationships

topic Compare this year vs Last Year sales (same week) only if we sold more than 4 last year in QlikView

Compare this year vs Last Year sales (same week) only if we sold more than 4 last year