topic Re: Problem using set analysed in QlikView

Problem using set analysed

bert_geraerts — Mon, 03 Dec 2018 11:31:09 GMT

I have the following problem using set analyses, and I was wondering if you guys could help me.

We have a table that contains all purchase lines in our company, for example:

IN_ProductNr
IN_DeliveryDate
IN_OrderNr
IN_QTY

In a charttable we would like to show the next delivery (based on DeliveryDate) and the QTY this order contains.
Showing the next delivery date isn't any problem, but I don't seem to be able to show the correct QTY.

When I use the following code, I only get a correct value when only one product is selected.
Otherwise the result is always 0.

sum({$<IN_DeliveryDate={"$(=Min(IN_DeliveryDate))"}>}IN_QTY)

When I use the following code then the QTY is the total for all outstanding orders.

sum({$<IN_DeliveryDate={"=Min(IN_DeliveryDate)"}>}IN_QTY)

Could somebody please tell me is this is possible and how to do this?

Re: Problem using set analysed

Gabriel — Mon, 03 Dec 2018 11:38:46 GMT

Hi,

Is there are way to attached sample data?

In the absence of sample data, try "=$()"as below

sum({$<IN_DeliveryDate={"=$(=Min(IN_DeliveryDate))"}>}IN_QTY)

If you can attach sample data it will help

Re: Problem using set analysed

sunny_talwar — Mon, 03 Dec 2018 12:06:55 GMT

May be you need this

FirstSortedValue(Aggr(Sum(IN_QTY), IN_ProductNr, IN_DeliveryDate), -Aggr(IN_DeliveryDate, IN_ProductNr, IN_DeliveryDate))

Re: Problem using set analysed

bert_geraerts — Mon, 03 Dec 2018 12:17:53 GMT

Hi Stalwar1,

That is working perfectly!

Could you also provide the sollution if I needed the second ranked value?
So the delivery QTY for the second shipment ranked by delivery date.

Thx

Re: Problem using set analysed

sunny_talwar — Mon, 03 Dec 2018 12:19:29 GMT

Add a 2 at the end like this

FirstSortedValue(Aggr(Sum(IN_QTY),  IN_ProductNr, IN_DeliveryDate), -Aggr(IN_DeliveryDate, IN_ProductNr, IN_DeliveryDate), 2)