explain how E() works

[ali_hijazi]

Hello

got this expression:

SUM(AGGR( COUNT(distinct

                                {

                <

                 YEAR=,MONTH=,QUARTER=,

                TRANS_DATE={">=$(vTwelve_Months_Back_Selected_Date) <=$(vWon_Lost_Max_Date)"}

                ,CUSTOMER_KEY=e({<TRANS_TYPE={'INVOICE'},YEAR=,MONTH=,QUARTER=, TRANS_DATE={">=$(vTwelve_Months_Back_Selected_Date) <=$(vWon_Lost_Max_Date)"}>}CUSTOMER_KEY)

  ,CUSTOMER_KEY={"=FIRST_INVOICE<$(vTwelve_Months_Back_Selected_Date)"}

            >

        }DISTINCT    CUSTOMER_KEY),MONTH_YEAR, CUSTOMER_KEY))

I want to count the customers who were not invoiced between 2 dates

however the E() seems not to work unless I add dummy records for all customers with amount =0 and then update the above expression with CUSTOMER_KEY ={"=SUM({<.......>}AMOUNT)=0"} then I get the correct result

but how can I accomplish this without adding the dummy records

[Anonymous]

Hi Ali,

See this post: P() &amp; E() and where do you use them?

Regards!!

[marcus_sommer]

Maybe you need to switch and/or to add additionally the conditions (or parts from them) to the outer-aggregation, see also: Set Analysis in the Aggr function.

- Marcus

[Anonymous]

Hi

If you would like check how E() works please follow  below example

1)I have added 'Add Caluclated Dimension' as below (I have added the attachment as well)

=if(Aggr(Sum(1), CUSTOMER_KEY)>0, 'Selected: $(=Concat(DISTINCT CUSTOMER_KEY, ', '))',

  'Others: $(=Concat({<CUSTOMER_KEY=E({$})>} DISTINCT CUSTOMER_KEY, ', '))')

2)  FYI (your expression)

SUM(AGGR( COUNT(distinct

{

<

YEAR=,MONTH=,QUARTER=,

TRANS_DATE={">=$(vTwelve_Months_Back_Selected_Date) <=$(vWon_Lost_Max_Date)"}

              , CUSTOMER_KEY=e({<TRANS_TYPE={'INVOICE'},YEAR=,MONTH=,QUARTER=, TRANS_DATE={">=$(vTwelve_Months_Back_Selected_Date) <=$(vWon_Lost_Max_Date)"}>}CUSTOMER_KEY)

  , CUSTOMER_KEY={"=FIRST_INVOICE<$(vTwelve_Months_Back_Selected_Date)"}

>

        }DISTINCT  CUSTOMER_KEY),MONTH_YEAR, CUSTOMER_KEY))

    *Please look at FIRST_INVOICE , If this is a Date field you need to mention with  $ and date like "$(=Date(FIRST_INVOICE,'MM/DD/YYY'))

  * SUM(AGGR( COUNT(distinct..........................  Please check the Associative Fields for this  SUM and COUNT ???

[ali_hijazi]

FIRST_INVOICE is a date field (all date fields are numbers in the qvw file of mine)

what is not working is the part where I put E()

the same expression works well if I add dummy records with zero amount and put instead of E() the following:

CUSTOMER_KEY={"=SUM({<same_condition_as_above>}amount)=0"} then it woks

so the E() is not working as expected

[swuehl]

You should not use a field modifier twice within the same set modifier (i,e, in your sample, you are modifying CUSTOMER_KEY twice).

Only one of the field modifier (I believe the second one) will be used.

If you need to combine the two modifications, use set operators.