Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to make an inventory ageing analysis with a rolling balance based on some defined time buckets.
The raw data is inventory transaction details. I have been able to make the report in a vertical pivot format, but struggle to make it in a horizontal pivot format.
I have attached a sample qlikview app with data and an excel sheet with the logic I am trying to acheive.
All helps and hints appreciated.
Regards,
Freddy
Hi Freddy,
You can use the below expression to get the desired result.
if(
rangesum(
if(isnull(aggr(nodistinct Sum({<TransactionType={'5'}>} QtyTrans), Item)),
Before(aggr(nodistinct Sum({<TransactionType={'5'}>} QtyTrans), Item),1,1),
aggr(nodistinct Sum({<TransactionType={'5'}>} QtyTrans), Item))
,
Before(Sum({<TransactionType={'1','3'}>}QtyTrans),0,1),
Before(Sum({<TransactionType={'1','3'}>}QtyTrans),1,1),
Before(Sum({<TransactionType={'1','3'}>}QtyTrans),2,1),
Before(Sum({<TransactionType={'1','3'}>}QtyTrans),3,1)
)
< 0,0,
rangesum(
if(isnull(aggr(nodistinct Sum({<TransactionType={'5'}>} QtyTrans), Item)),
Before(aggr(nodistinct Sum({<TransactionType={'5'}>} QtyTrans), Item),1,1),
aggr(nodistinct Sum({<TransactionType={'5'}>} QtyTrans), Item))
,
Before(Sum({<TransactionType={'1','3'}>}QtyTrans),0,1),
Before(Sum({<TransactionType={'1','3'}>}QtyTrans),1,1),
Before(Sum({<TransactionType={'1','3'}>}QtyTrans),2,1),
Before(Sum({<TransactionType={'1','3'}>}QtyTrans),3,1)
)
)
Attaching the modified QVW file too.
Regards,
Sajeevan
Hi Freddy,
You can use the below expression to get the desired result.
if(
rangesum(
if(isnull(aggr(nodistinct Sum({<TransactionType={'5'}>} QtyTrans), Item)),
Before(aggr(nodistinct Sum({<TransactionType={'5'}>} QtyTrans), Item),1,1),
aggr(nodistinct Sum({<TransactionType={'5'}>} QtyTrans), Item))
,
Before(Sum({<TransactionType={'1','3'}>}QtyTrans),0,1),
Before(Sum({<TransactionType={'1','3'}>}QtyTrans),1,1),
Before(Sum({<TransactionType={'1','3'}>}QtyTrans),2,1),
Before(Sum({<TransactionType={'1','3'}>}QtyTrans),3,1)
)
< 0,0,
rangesum(
if(isnull(aggr(nodistinct Sum({<TransactionType={'5'}>} QtyTrans), Item)),
Before(aggr(nodistinct Sum({<TransactionType={'5'}>} QtyTrans), Item),1,1),
aggr(nodistinct Sum({<TransactionType={'5'}>} QtyTrans), Item))
,
Before(Sum({<TransactionType={'1','3'}>}QtyTrans),0,1),
Before(Sum({<TransactionType={'1','3'}>}QtyTrans),1,1),
Before(Sum({<TransactionType={'1','3'}>}QtyTrans),2,1),
Before(Sum({<TransactionType={'1','3'}>}QtyTrans),3,1)
)
)
Attaching the modified QVW file too.
Regards,
Sajeevan
Hi Sajeevan,
Thanks a lot. This worked perfectly.
Could you please explain the logic of the nodistinc function?
Regards,
Freddy
Hi Freddy,
The manual describes the logic of NODISTINCT as below.
If the expression argument is preceded by the NODISTINCT qualifier, each combination of dimension values may generate more than one return value, depending on underlying data structure. If the expression argument is preceded by the DISTINCT qualifier or if no qualifier is used at all, each combination of dimension values will generate only one return value.
Best Regards,
Sajeevan