Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
djbloiss
Contributor III
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

0 Replies