topic Re: Problem AGGR with condition in QlikView

Problem AGGR with condition

Markus_QV — Wed, 05 Sep 2018 09:27:43 GMT

Hi,

I have a Problem with a 'aggr' formula.

I want to count all the customer with sales > 0.

In my example this will be 75 (count({< CustID = {"=sum(Sales) > 0"}>}CustID)) for store 0592.

All customers are 78 (Count CUSTID)) for 0592

(as you can see in the pic & Excel-File).

Now I want a list of all my stores (counted by store and market).

When I selected the store 0592 I will get 75.

aggr( count( {< CUSTID= {"=sum(Sales) > 0"}>}CustID), Store, Market )

But if de-select the store and all my stores are shown, I will get the result 78 for the store 0592.

What I need to change that even in that overall list it will be 75?

Can someone pls. help me?

Best Regards

Markus

Re: Problem AGGR with condition

kaushiknsolanki — Wed, 05 Sep 2018 09:56:14 GMT

Hi,

Try normal expression which you used earlier. i.e.

(count({< CustID = {"=sum(Sales) > 0"}>}CustID))

Just add Market as dimension along with Store.

Regards,

Kaushik Solanki

Re: Problem AGGR with condition

Markus_QV — Wed, 05 Sep 2018 10:04:35 GMT

Hi Kaushik,

no Change.

Regrads

Markus

Re: Problem AGGR with condition

kaushiknsolanki — Wed, 05 Sep 2018 10:10:26 GMT

Hi,

Can you share full data. (I am unable to replicate the issue with given data)

Regards,

Kaushik Solanki

Re: Problem AGGR with condition

andrey_krylov — Wed, 05 Sep 2018 10:11:11 GMT

Hi, Markus. I think there must be something else, I made a sample with your data and expressions... and got no error

Re: Problem AGGR with condition

Markus_QV — Wed, 05 Sep 2018 11:57:02 GMT

Hi Andrey,

I found the the Problem.

As you can see there is 1 cutomer who got sales in 2 stores (1 is negative and 1 is positive).

I need to know if the customer in one store is >0 (not in General).

The last column /formula in the 2nd row is not correct.

Can anyone help me with that?

aggr( count({< [Cust ID] = {"=sum(Sales)>0"}>}[Cust ID]), Market, Store)

Best Regards

Markus

Re: Problem AGGR with condition

andrey_krylov — Wed, 05 Sep 2018 13:32:26 GMT

I think I figured out the issue, Markus. Try this expression

Sum(If(Aggr(sum(Sales), Market, Store, [Cust ID] ) > 0, 1))

Re: Problem AGGR with condition

Markus_QV — Wed, 05 Sep 2018 13:34:31 GMT

Hi Andrey,

Yes. Perfect. Thank you very much.

Regards

Markus