topic Re: Set Analysis with Multiple Modifiers in QlikView

Set Analysis with Multiple Modifiers

asmithids — Tue, 11 Nov 2014 03:38:46 GMT

Hi,

I'm trying to pull the quantity on hand (QOH) from a table that contains QOH per year per period. The set analysis below has multiple modifiers to filter the QOH for the year and period. I'm not getting errors, but the result is 0 (zero). I'm not sure where my set expression is failing. Any assistance would be very much appreciated.

Thank you in advance.

=Num((Sum({$<FiscYear={$(=Max(FiscYear))}, FiscPer={$(=Max(FiscPer))}> } QtyOnHand)))

Re: Set Analysis with Multiple Modifiers

SergeyMak — Tue, 11 Nov 2014 03:43:01 GMT

Hi Alec,

It's hard to say without app.

Please share your app or data sample

Regards,

Sergey

Re: Re: Set Analysis with Multiple Modifiers

asmithids — Tue, 11 Nov 2014 03:56:01 GMT

Hi Sergey,

See attached app. I added a Bookmark called Item: 8202030. This particular item has QOH.

Thank you,

Alec

Re: Set Analysis with Multiple Modifiers

anbu1984 — Tue, 11 Nov 2014 04:05:05 GMT

=Num((Sum({$<FiscYear={$(=Max(FiscYear))}, FiscPer={$(=Date(Max(FiscPer),'MMM'))}> } QtyOnHand)))

Re: Set Analysis with Multiple Modifiers

asmithids — Tue, 11 Nov 2014 04:23:02 GMT

Anbu,

Thank you for the fix. I am now getting numbers. However, the quantity is not agreeing to the source system. Also, when I filter on any FiscPer, the quantity does not change. It is remaining at 1,352.

Re: Set Analysis with Multiple Modifiers

anbu1984 — Tue, 11 Nov 2014 04:25:36 GMT

=Num((Sum({$<FiscYear={$(=Max(FiscYear))}, FiscPer={$(=Month(Date#(Max({<FiscYear = {$(=Max(FiscYear))}>} FiscPer),'MM')))}> } QtyOnHand)))

Re: Re: Set Analysis with Multiple Modifiers

anbu1984 — Tue, 11 Nov 2014 04:33:24 GMT

Check this

Re: Re: Set Analysis with Multiple Modifiers

SergeyMak — Tue, 11 Nov 2014 04:41:58 GMT

Alec,

I just can't understand why do you use set analysis here..

Try to use the following expression

=Num(Sum(QtyOnHand))

PFA

Re: Set Analysis with Multiple Modifiers

asmithids — Tue, 11 Nov 2014 04:48:04 GMT

Works!!! Thank you Anbu!!!

Re: Set Analysis with Multiple Modifiers

asmithids — Tue, 11 Nov 2014 04:57:12 GMT

Sergey,

You are correct in what you are saying. The full purpose of the expression will be to calculate inventory turnover. I need to first compute the average inventory for the period - (beginning inventory + ending inventory)/2

Now that I have the current period working, I need to calculate the beginning inventory which is the ending inventory from the previous period using -1 in the modifier.