It's hard!!

[Marcoimp]

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?

[Marcoimp]

I Add an example, heeeelp!

[Gysbert_Wassenaar]

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)

[Marcoimp]

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.

[Anonymous]

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

[swuehl]

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

[Gysbert_Wassenaar · Accepted solution]

Try:

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

           MERCH_MONTH={"<=6"}

         >} DISTINCT

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

                       MERCH_MONTH=

                      >}

                 NETSALEBUDGET)

               ,CONTACTID,COD_STORE)>=2500,

            CONTACTID)

      )

[Marcoimp]

PERFECT!

Thank you!

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