topic Re: Re: Re: Pivot question: Alert when does the stock runout (AGGR?) in QlikView

Pivot question: Alert when does the stock runout (AGGR?)

Anonymous — Wed, 02 Jul 2014 10:21:14 GMT

Dear specialist,

I got a pivot table with 3 dimensions:

Product

Stock

Weeknumber (Year and Week)

For every Product I got an expression which calculates te stock need for every week, which results in this pivot:

What I would like is a Pivot which shows me when my stocks runs out like this:

For example product 001-0028 the current stock is 223, I got enough for 2014-24 and 2014-15, but not enough for the next week so from there it needs to become red.

What Expression should I use for the background colour to get this?

Thanks,

Dennis.

Re: Pivot question: Alert when does the stock runout (AGGR?)

Gysbert_Wassenaar — Wed, 02 Jul 2014 12:11:10 GMT

Maybe something like =if(rangesum(before(total sum(WeekAmount),0,rowno(total)) > Stock, red())

Re: Pivot question: Alert when does the stock runout (AGGR?)

Anonymous — Wed, 02 Jul 2014 13:31:28 GMT

Thanks Gysbert, but I dont get it to work with that, the expression you suggested returns 0

What I am trying now, I add a field Year_week_number, which I want to use in a set analyses in combination with AGGR.

What I got now is :

AGGR(SUM({$<YEAR_WEEK_NUMBER={' < $(YEAR_WEEK_NUMBER)'}>}AMOUNTNEEDED), PRODUCT)

This way I am trying to get a sum of all the AMMOUNTNEEDED, of the product of the weeks with a lower number then week in the pivot. From their I will try further.

Any suggustions?

Re: Pivot question: Alert when does the stock runout (AGGR?)

Gysbert_Wassenaar — Wed, 02 Jul 2014 13:41:52 GMT

I don't think set analysis will work. The set is calculated at the chart level so you won't get a set per dimension value of YEAR_WEEK_NUMBER. Perhaps you can post a small example document with some data.

Re: Re: Pivot question: Alert when does the stock runout (AGGR?)

Anonymous — Wed, 02 Jul 2014 14:28:30 GMT

Thank for explaining.

Here is an example document of how my charts looks like.

Thanks.

Re: Re: Re: Pivot question: Alert when does the stock runout (AGGR?)

Gysbert_Wassenaar — Thu, 03 Jul 2014 08:13:04 GMT

Try this one:

if(rangesum(before(total sum(Needed)*sum(Totaal),0,ColumnNo(total)))>CurrentStock,rgb(250,125,125))

Re: Re: Re: Pivot question: Alert when does the stock runout (AGGR?)

Anonymous — Thu, 03 Jul 2014 08:56:41 GMT

Perfect! Thanks again Gysbert!