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: 
whiteymcaces
Partner - Creator
Partner - Creator

List of Zero Transactions with Set Analysis

Hi,

I would like to create a straight table that lists all the Stock Items that have not sold in the past x months.

I am using this expression:

If(Sum({<Year=, Month=, MonthYear=, TranDate = {'>=$(vMthsBack) <=$(vMaxDate)'}, [Transaction Type] = {'Issue', 'FO Issue'}>} Qty) = 0, 1, 0)

I am finding that the [Transaction Type] = {'Issue', 'FO Issue'} part of the Set Expression is causing the result of my Expression to not work because there is no association with any Stock Item and the 'selections' made in the Set Expression.


Does anybody have any suggestions or a different way to achieve this?

1 Solution

Accepted Solutions
whiteymcaces
Partner - Creator
Partner - Creator
Author

Hi Olivier,

Thanks for your reply.

Based on your input I have modified my approach and came up with this expression:-

Sum({<[Stock Code] = E({<MonthYear=, TranDate = {'>=$(vMthsBack) <=$(vMaxDate)'}, [Transaction Type] = {'Issue', 'FO Issue'}>})>} 1)

An an example for people reading this my other solution was to create a flag in the script that puts a '1' in the flag field [isIssue], based on the [Transaction Type] and then used this expression:-

If(Sum({<MonthYear=, TranDate = {'>=$(vMthsBack) <=$(vMaxDate)'}>} Qty * isIssue) = 0, 1, 0)

I prefer the first solution as there is less script work ([Transaction Type] is in a different table to [isIssue], so addition Mapping was required, plus the first solution uses slightly less RAM and calc time.

View solution in original post

2 Replies
ogautier62
Specialist II
Specialist II

Hi,

when you say not sold:

that means that there is no transaction ?

if so :   StockItem = E({<TranDate = {'>=$(vMthsBack) <=$(vMaxDate)'} >})      to say exclude all item with no transaction between these two dates)

regards

whiteymcaces
Partner - Creator
Partner - Creator
Author

Hi Olivier,

Thanks for your reply.

Based on your input I have modified my approach and came up with this expression:-

Sum({<[Stock Code] = E({<MonthYear=, TranDate = {'>=$(vMthsBack) <=$(vMaxDate)'}, [Transaction Type] = {'Issue', 'FO Issue'}>})>} 1)

An an example for people reading this my other solution was to create a flag in the script that puts a '1' in the flag field [isIssue], based on the [Transaction Type] and then used this expression:-

If(Sum({<MonthYear=, TranDate = {'>=$(vMthsBack) <=$(vMaxDate)'}>} Qty * isIssue) = 0, 1, 0)

I prefer the first solution as there is less script work ([Transaction Type] is in a different table to [isIssue], so addition Mapping was required, plus the first solution uses slightly less RAM and calc time.