topic Trying to get TOTAL SALES... in QlikView

Trying to get TOTAL SALES...

hopkinsc — Thu, 08 Sep 2011 13:34:04 GMT

Hi, i have a Stock Movement app which i am having trouble displaying TOTAL SALES on.

In the app the user selects the 'Stock Movement' that they wish to view..

i.e.

Wastage

Reductions

Goods Received

etc

I am loading in a product sales table and movement table.

After the user selects a Movement Reason (lets say Wastage for this example), only the sales for the day of the wastage is displayed.

I want the app to display the TOTAL SALES for the product regardless of any other movement or not.

See example below of the WRONG result..

And the result i want to see is 1 line (as its only the top line that has waste against it) but with the total sales of 44, not 7.

Does anyone know how i can achieve this?

Trying to get TOTAL SALES...

Thu, 08 Sep 2011 13:59:07 GMT

You should be able to get this with teh aggr function

Try

aggr(NODISTINCT Sum(Total Sales Qty), Product Code)

Trying to get TOTAL SALES...

hopkinsc — Thu, 08 Sep 2011 14:13:16 GMT

Hi, thanks for your reply.

That expression only gave me the total sales if NO movement reason is selected. If i select Wastage, then it displays total sales of 7 which is the sales of the day the wastage was performed.

see below

This is when i select a movement reason (Wastage)

This is where no movement selections have been made..

So the result i want is the 2nd pic but when i reason is selected.

Trying to get TOTAL SALES...

Thu, 08 Sep 2011 15:31:56 GMT

try changing the sum( to sum(TOTAL ...

If that does not work can you post me some sample data and i will work at it here?

Re: Trying to get TOTAL SALES...

hopkinsc — Thu, 08 Sep 2011 15:50:12 GMT

Hi mark, that didn't work either...

attached is a sample.

If you look at it as it is, the sales values are correct.

but if you select GFSU from the bottom left, the sales value changes to the sales where GFSU took place. I want the sales values to always display the sales correctly for the dates selected...

Thanks for looking..

Re: Trying to get TOTAL SALES...

Thu, 08 Sep 2011 15:52:28 GMT

Hi, I am new to Qlikview myself but I know you can do this, I think it is via Set Analysis where you can set a value based on selections but ignoring the selections in a specific field.

I will work that through on your file and advise, sorry for delay !!

Re: Trying to get TOTAL SALES...

hopkinsc — Thu, 08 Sep 2011 15:55:19 GMT

Thats great, thanks mark. No rush, i am going home now, back in on Monday

Thanks again.

Re: Trying to get TOTAL SALES...

Thu, 08 Sep 2011 16:00:40 GMT

If i have got what you are after

sum(TOTAL {$<[Stock Reason] = >} SalesQuantity)

should work.

This will preserve all of your other selections, Dept etc but will ignore any selections made in the Stock Reason Field.

Re: Trying to get TOTAL SALES...

hopkinsc — Mon, 12 Sep 2011 08:21:15 GMT

Morning Mark, thanks for this expression.

It works as expected if i am only viewing 1 product. But if i am viewing multiple products then it sums up the complete sales for all products viewed.

I have attached another sample (as the first sample only had 1 product)

If you look at the chart as it is, you will see that the sales on all prodycts are the same (161)

If you then select the first product (Product code ID8543) the sales drop to 10 (which is correct for this product) from this point on the expression works. But i would need it to work no matter how many products were being displayed.

Can you help with this?

Re: Trying to get TOTAL SALES...

hopkinsc — Mon, 12 Sep 2011 08:37:55 GMT

Sorry Mark, ignore my last reply i removed the TOTAL from the expression and it works as expected. Thanks for your help

Re: Trying to get TOTAL SALES...

Mon, 12 Sep 2011 14:39:23 GMT

Good show. are you able to flag this as answered?

Cheers