Help with rolling balance in inventory ageing analysis

[#1]

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

[#2 · Accepted solution]

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

[#3]

Hi Sajeevan,

Thanks a lot. This worked perfectly.

Could you please explain the logic of the nodistinc function?

Regards,

Freddy

[#4]

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