topic Re: Calculation problem in QlikView

Calculation problem

Mon, 21 Oct 2013 12:14:00 GMT

Hi everyone,

I came across a quiet weird problem. I posted a picture so you should look at it I have a min Date to every product and I try to calculate now how many pieces I had in stock on the min date of my product. so the product 712486 I had in stock on the 05.06.2012 in stock the first time and I had 10 piece of it. so my second product 726751 I had in stock the first time on the 30.10.2012 so far so good but QV still calculates the pieces i had in stock for the 05.05.2012 but I want it for 30.10.2012. Can anyone help me to fix this pivot table??

regards,

Re: Calculation problem

thanstad — Mon, 21 Oct 2013 13:02:03 GMT

What if you make a simple set analysis as two separate expressions

=sum({$<Period={$(=MIN(Period))}>} Menge)

AND

=sum({$<Period={$(=MAX(Period))}>} Menge )

Tormod Hanstad

Re: Calculation problem

Mon, 21 Oct 2013 13:05:53 GMT

If you looked at the picture I posted if I do the set analysis sum({<min(...)>}MENGE) it only calculates the stock right for the product we got delivered first. so the pivot table doesnt use the min(date) for each row right in the set analysis

Re: Calculation problem

Gysbert_Wassenaar — Mon, 21 Oct 2013 14:06:09 GMT

The set analysis expression calculates one set for the entire chart, not a set per row. Perhaps the firstsortedvalue can be used: firstsortedvalue(MENGE, DATUM)

Re: Calculation problem

Anonymous — Mon, 21 Oct 2013 14:14:26 GMT

Magdalena,

Either try the suggestion from GW, or create a field in the data model:

min(Datum) as FirstOrderDatePerProduct

Regards,

Michael

Re: Calculation problem

Tue, 22 Oct 2013 11:24:02 GMT

hi,

thanks for the quick response but all you suggestions didn't work because also when I save the firstoderdateperproduct i still come across the same issue. because every produkt has a different FirstOrderDatePerProduct. i am using now this in my set analysis or did I understand something wrong?

regards,

Re: Calculation problem

Anonymous — Tue, 22 Oct 2013 12:24:09 GMT

It is expected that each Product has its own FirstOrderDatePerProduct. The expression in your table probably should be:
sum(if(FirstOrderDatePerProduct=DATUM, MENGE))
But I could be wrong without knowing what the data is about and what MENGE means. In this case the problem is not with the pivot table but in the overall data model design.

Regards,
Michael