topic Re: It's hard!! in QlikView

It's hard!!

Marcoimp — Wed, 23 Oct 2013 13:25:20 GMT

I want to count the unique CONTACTID for this first half year, with a LastYear SALESVALUE>3000$, I do in this way:

=COUNT({$<

F_TYPE={'A'},

YEAR={2013},

MONTH={"<=6"},

SALESVALUE={">0"},

CONTACTID={"=SUM({$<

F_TYPE={'A'},

YEAR={2012},

MONTH=

>} NETSALEBUDGET)>=3000"}

>})

>} DISTINCT CONTACTID)

The question is: all seems to be ok, but, when I use this formula in a grid, the sub SUM() (in bold) function seems to count every unique CONTACTID of the whole world and not only for the region code of the dimension line......... how can I correct this?

Re: It's hard!!

Marcoimp — Thu, 24 Oct 2013 08:49:33 GMT

I Add an example, heeeelp!

Re: Re: It's hard!!

Gysbert_Wassenaar — Thu, 24 Oct 2013 09:41:25 GMT

If you don't select a COD_STORE then the inner sum is calculated over the CONTACTID's of all COD_STORE's. This sum value is then compared to 2500. But if you select a COD_STORE then the inner sum is calculated over only the CONTACTID's for that selected COD_STORE. You can get the same results if you change the expression like this:

=COUNT({$<

MERCH_YEAR={2013},

MERCH_MONTH={"<=6"},

CONTACTID={"=SUM({$<

MERCH_YEAR={2012},

MERCH_MONTH=,

COD_STORE=

>} NETSALEBUDGET)>=2500"}

>} DISTINCT CONTACTID)

Re: Re: It's hard!!

Marcoimp — Thu, 24 Oct 2013 09:49:03 GMT

Thank you,

yes, but my goal is to know the value for the single store, not for the whole world...

In the example, the correct code for COD_STORE "BV2706" is 65, not 106.

Re: It's hard!!

Anonymous — Thu, 24 Oct 2013 10:02:09 GMT

Hi,

I looked at your sample file.
in the "List Box" you are counting the "COD_STOR" with NETSALESBUDGET >= 2500.
While in the column formula ""#TOP CLIENTS 1H 2013 (SUM)" you are counting the "COD_STOR" with NETSALESBUDGET >= 3000

Re: Re: Re: It's hard!!

swuehl — Thu, 24 Oct 2013 10:09:24 GMT

Maybe like this, using a combined KEY made of CODE_STORE and CONTACTID

Re: Re: It's hard!!

Gysbert_Wassenaar — Thu, 24 Oct 2013 10:14:31 GMT

Try:

=COUNT({$< MERCH_YEAR={2013},

MERCH_MONTH={"<=6"}

>} DISTINCT

if(aggr(sum({<MERCH_YEAR={2012},

MERCH_MONTH=

NETSALEBUDGET)

,CONTACTID,COD_STORE)>=2500,

CONTACTID)

)

Re: It's hard!!

Marcoimp — Thu, 24 Oct 2013 12:00:36 GMT

PERFECT!

Thank you!

When you know the answer it seems simple!!!!!!!