Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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
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.