topic Re: Sales percentage in ranges by product in QlikView

Sales percentage in ranges by product

Mon, 15 Jun 2015 16:00:11 GMT

Hello

I need to create a table that show the follow:

Product	< 20%	20% - 50%	51% - 80%	> 90%
Product A	8	25	80	20
Product B	30	70	15	2
Product C	55	15	15	7
Others	45	100	25	15

In 8 stores the Product A represent less than 20% of the total sales

In 70 stores the Product B represent between 20 and 50 percent of the total sales

In 7 stores the Product C represent more than 90 percent of the sales

etc.

In the Database I have a Fact Table with the detailed transacctions, the fact table is like this:

Date	Store	Product	Transacction ID	Amount
15/06/2015	Store 1	Product A	00000001	$ 10.75
10/04/2015	Store 3	Product B	00000002	$ 28.35
14/03/2015	Store 2	Product A	00000003	$ 50.38
10/04/2015	Store 1	Product C	00000004	$ 45.22

Someone know how can I elaborate this kind of anlaysis

Best Regards

Re: Sales percentage in ranges by product

Gysbert_Wassenaar — Mon, 15 Jun 2015 16:27:05 GMT

Can you post a Qlikview document that demonstrates the problem?

Re: Sales percentage in ranges by product

Mon, 15 Jun 2015 16:45:30 GMT

I don't have the document already, I only have the fact table and the dimnensions

I'm tring at this point to bulid the analysis

Re: Sales percentage in ranges by product

sinanozdemir — Mon, 15 Jun 2015 18:22:51 GMT

I am not sure if my approach gives you a starting point. However, here is my approach:

For each percentage bucket, I created different expressions and I just labeled columns based on the percentage brackets. If an expression falls into a percentage bucket, then I counted the distinct number of stores.

Hope this helps.

Re: Sales percentage in ranges by product

Mon, 15 Jun 2015 20:03:21 GMT

Sorry I made wrong my question:

All is based in the transacctions

Let say The Store A make 200 transacctions: 10 of Product A, 140 of Product B and 50 of Product C. This store make lees than 20% of transacctions of the Product A.

My analysis need to tell me how many of this stores I have (Stores that sells less than 20% of transacctions of certain product and the same for the others ranges.

Is this a Little bit clear??

Re: Sales percentage in ranges by product

sinanozdemir — Mon, 15 Jun 2015 20:23:08 GMT

Well, in this case we can change the function to count transaction IDs. Please see the below screenshot:

Hope this gives you an idea of how to approach your problem.

Re: Sales percentage in ranges by product

Mon, 15 Jun 2015 21:18:07 GMT

Hi Is not working because Is not making the Formula by each store, is making a global formula

Re: Sales percentage in ranges by product

sinanozdemir — Mon, 15 Jun 2015 23:44:48 GMT

It seems like I had a flaw in my formula. How about the below:

Thanks

Re: Sales percentage in ranges by product

Tue, 16 Jun 2015 22:06:02 GMT

Hello

I can I send you a file with the detail??

Regards

Re: Sales percentage in ranges by product

Tue, 16 Jun 2015 22:14:48 GMT

Hi I publish the file with the datail of this case under Porcentage transacctions by store (Attachment)

Regards

Re: Sales percentage in ranges by product

sinanozdemir — Wed, 17 Jun 2015 15:04:22 GMT

Alright, I am giving another shot to this problem.

What I did was I took your spreadsheet and used "Table Look Like" tab because I couldn't find any connection between Provider# and Provider name, and also Store name. I cleaned up "Table Look Like" data, please see attached.

I then imported that spreadsheet to QlikView.

As you can see, I first summed all transactions by STORE then left joined to Temp table.

In the second "Main" table, I divided each individual transaction by the summed of transactions by each store.

In the front end, I took the average of the new field "Product Store %" and for each percentage bucket I placed below expression.

At this point, this is my approach. I am sure there is a more efficient way to do this without all this.