Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
djbloiss
New Contributor III

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

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