topic Re: Need Assistance in Count Function in QlikView

Need Assistance in Count Function

Anonymous — Wed, 09 Sep 2015 08:31:30 GMT

Hi Experts,

I do have table as below and need to count the customers who has returned the product from total sales.

Product	Year	Month	Value
Item 1	2015	JAN	-1
Item 2	2015	JAN	10
Item 3	2015	JAN	20
Item 4	2015	JAN	30
Item 5	2015	JAN	-40
Item 6	2015	JAN	50
Item 7	2015	JAN	60
Item 1	2015	JAN	70
Item 2	2015	JAN	-80
Item 3	2015	JAN	90
Item 4	2015	JAN	-100
Item 5	2015	JAN	110
Item 6	2015	JAN	120
Item 7	2015	JAN	130
Item 1	2015	JAN	140
Item 2	2015	JAN	150
Item 3	2015	JAN	-160
Item 4	2015	JAN	170
Item 5	2015	JAN	180
Item 6	2015	JAN	-190
Item 7	2015	JAN	200

The out put which i would like to get as below :

In Label:

No Returned Products: 2

Month	Total Value	Total
Item 1	209
Item 2	80
Item 3	-50
Item 4	100
Item 5	250
Item 6	-20
Item 7	390
JAN Total	95

Thanks

Raju .K

Re: Need Assistance in Count Function

sunny_talwar — Wed, 09 Sep 2015 08:39:58 GMT

Looking for this?

Expression: =Sum(If(Aggr(Sum(Value), Product) < 0, 1, 0))

Re: Need Assistance in Count Function

Anonymous — Wed, 09 Sep 2015 08:40:13 GMT

you would the Basic table with

SalesPerMonth:

noconcatenate load

Month,

Product,

sum(Value)

resident Yourtable

Group by Month, Product

drop table yourtable

But how do you recognize returned products? by negative sum per month?

then in a textbox

if (sum(Value)<0, Count(Product))

Re: Need Assistance in Count Function

qlikviewwizard — Wed, 09 Sep 2015 09:09:35 GMT

Hi Raju,

Try like this in Pivot Table.

Hope this will help you. Thank you.

Re: Need Assistance in Count Function

Anonymous — Wed, 09 Sep 2015 10:18:32 GMT

Excellent Sunny Thanks a lot

Re: Need Assistance in Count Function

Anonymous — Wed, 09 Sep 2015 10:19:02 GMT

Thanks Rudolf

Re: Need Assistance in Count Function

Anonymous — Wed, 09 Sep 2015 10:19:16 GMT

Thanks Mate

Re: Need Assistance in Count Function

sunny_talwar — Wed, 09 Sep 2015 10:19:40 GMT

No problem

I am glad I was helpful.

Best,

Sunny