topic Re: set analysis with conditions in QlikView

set analysis with conditions

stephenedberkg — Tue, 15 Sep 2015 08:25:42 GMT

i want to calculate the return product analysis.

sum(sales) <0 that is my return sales.

i want to take the count of total number of return product.

how to write this concept in set analysis formula?

Thanks in Advance

Re: set analysis with conditions

sunny_talwar — Tue, 15 Sep 2015 08:28:25 GMT

May be this:

=Count({<OrderID = {"=Sum(Sales) < 0"}>}OrderID)

Re: set analysis with conditions

alexandros17 — Tue, 15 Sep 2015 08:32:27 GMT

Try with:

Sum(If(sum(sales)<0,1,0))

Re: set analysis with conditions

nagarjuna_kotha — Tue, 15 Sep 2015 08:33:42 GMT

Can u share sample data ?

Re: set analysis with conditions

Anonymous — Tue, 15 Sep 2015 08:35:01 GMT

Could you please share sample qlikview file?

Re: set analysis with conditions

Anonymous — Tue, 15 Sep 2015 08:35:46 GMT

maybe:

=Count (If(Aggr(Count(unit.id),Sales)>1,unit.id) )

Re: set analysis with conditions

jonathandienst — Tue, 15 Sep 2015 08:37:30 GMT

Set analysis is a way of filtering the data prior to evaluating the expression. In this case, I can see a filter criterion, but what would you like to filter? How are sales identified and grouped? The sum is over what dimensions?

Something like this may or may not work depending on the above.

=Sum({<Customer={"=Sum(Sales) < 0"}>} Sales)

Re: set analysis with conditions

gautik92 — Tue, 15 Sep 2015 08:39:45 GMT

sum(if(Sum(Sales)<0,1,0))

Re: set analysis with conditions

stephenedberkg — Tue, 15 Sep 2015 08:41:05 GMT

(Sum({<Year = {$(=Max(Year))},TransactionType={'INVOICE'},Month=>} #InvNetSales) -

Sum({<Year = {$(=Max(Year))},TransactionType={'CreditNote'},Month= >} #InvNetSales)) <0

that is my return sales

i want to calculate the count of return product

Re: set analysis with conditions

stephenedberkg — Tue, 15 Sep 2015 08:42:27 GMT

(Sum({<Year = {$(=Max(Year))},TransactionType={'INVOICE'},Month=>} #InvNetSales) -

Sum({<Year = {$(=Max(Year))},TransactionType={'CreditNote'},Month= >} #InvNetSales)) <0

that is my return sales

i want to calculate the count of return product

Re: set analysis with conditions

gautik92 — Tue, 15 Sep 2015 08:49:23 GMT

try this

Sum(if((Sum({<Year = {$(=Max(Year))},TransactionType={'INVOICE'},Month=>} #InvNetSales) -

Sum({<Year = {$(=Max(Year))},TransactionType={'CreditNote'},Month= >} #InvNetSales)) <0,1,0))

Re: set analysis with conditions

gautik92 — Tue, 15 Sep 2015 09:05:20 GMT

if(

(Sum({<Year = {$(=Max(Year))},TransactionType={'INVOICE'},Month=>} #InvNetSales) -

Sum({<Year = {$(=Max(Year))},TransactionType={'CreditNote'},Month= >} #InvNetSales)) <0,count(Distinct CustomerID)

)

Re: set analysis with conditions

jonas_rezende — Tue, 15 Sep 2015 12:51:23 GMT

Hi, Stephen Edberk.

Try

Count (Distinct {<Customer= {"=Sum({<Year = {$(=Max(Year))},TransactionType={'CreditNote'},Month= >} #InvNetSales)) <0"}>} Product)

Hope this helps!