topic ABC analysis with sorting in QlikView

ABC analysis with sorting

a_ligtvoet — Wed, 08 Oct 2014 09:51:00 GMT

Hello, I just started to use Qlikview and I have a big file with products and units send.

I want to create an ABC that the user can adjust.

I've looked on the community to find some answers and the best result I got is working for the ABC part, but I cannot sort the values descending.

I've attached a sample file with two tables, 1 with items (item and itemsale) and 1 with products (product and productsale).

The information is the same, only the products are loaded from largest to smallest (descending).

Item1 matches with Product1, Item2 with Product2, etc.

The tabel Itemsale should show the same result as the Productsale (without sorting the results in script, because in the actual file I want to create 2 tables with different ABC set ups (picks (count) and units (sum))).

So in this sample file Item2 (sale 3000) should be on the second line as an A item, and not a B.

Can anybody help me?

Re: ABC analysis with sorting

Gysbert_Wassenaar — Wed, 08 Oct 2014 13:33:58 GMT

See attached example.

Re: ABC analysis with sorting

a_ligtvoet — Wed, 08 Oct 2014 14:02:06 GMT

Hi Gysbert,

this is what I was looking for.

Can you give a short explanation of the formula rank(sum({1}ITEMSALE)) ?

Expecially what the {1} means?

Thanks.

Re: ABC analysis with sorting

Gysbert_Wassenaar — Wed, 08 Oct 2014 14:05:07 GMT

The {1} means to ignore all selections. I added that so that selections don't influence the sorting. But perhaps it's not needed. You can try leaving it out.

Re: ABC analysis with sorting

a_ligtvoet — Wed, 08 Oct 2014 14:42:27 GMT

Okay, thanks for your quick reply!

Re: Re: ABC analysis with sorting

a_ligtvoet — Thu, 09 Oct 2014 13:46:50 GMT

Hi Gysbert,

maybe you can help me once more.

The ABC on units is now working and I also got it working on the count of product in the sample file.

Then I tried to use it in the file I'm working with and everything is working except the cumulative count (Picks from location).

And I find this very strange, because even though the Cum.Pick % is wrong the ABC is correct.

Can you help me with the cumulative count and explain why the ABC (table Picks) is still working?

I'm refering to the incorrect Cum.Pick % column, am I not?

Thanks in advance.

Re: ABC analysis with sorting

Gysbert_Wassenaar — Thu, 09 Oct 2014 16:17:22 GMT

You enabled Full Accumulation on the Cum.Pick % expression. If you disable that the amounts are accumulated correctly (afaict).