topic Re: explain how E() works in QlikView

explain how E() works

ali_hijazi — Wed, 17 Aug 2016 12:38:34 GMT

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

Re: explain how E() works

Anonymous — Wed, 17 Aug 2016 12:48:24 GMT

Hi Ali,

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

Regards!!

Re: explain how E() works

marcus_sommer — Wed, 17 Aug 2016 13:22:05 GMT

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

Re: explain how E() works

Anonymous — Wed, 17 Aug 2016 14:17:54 GMT

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 ???

Re: explain how E() works

ali_hijazi — Wed, 17 Aug 2016 19:03:08 GMT

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

Re: explain how E() works

swuehl — Wed, 17 Aug 2016 19:47:27 GMT

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.