Help in set analysis

ali_hijazi — Mon, 21 Nov 2016 09:46:39 GMT

Hello I have t pivot table

I want to show the customers not served in the selected month and year

I was able to get them using the following expression:

CustomerCode = E(

TrxType={'Sales'}

,TrxDate = P(TrxDate)

,Customer_Default_Salesman2 = P(Customer_Default_Salesman2)

,CusNum = {"=Sum({1<TrxType={'Sales'},TrxDate = P(TrxDate),Customer_Default_Salesman2 = P(Customer_Default_Salesman2)>} ValueUSD)>0"}

} CustomerCode)

the above expression gets the customers that are not included in the set where there is sales transactions with valueUSD >0

this is fine and I was able to get the last invoice for these customers as follows:

= max(

{

CustomerCode = E(

TrxType={'Sales'}

,TrxDate = P(TrxDate)

,Customer_Default_Salesman2 = P(Customer_Default_Salesman2)

,CusNum = {"=Sum({1<TrxType={'Sales'},TrxDate = P(TrxDate),Customer_Default_Salesman2 = P(Customer_Default_Salesman2)>} ValueUSD)>0"}

} CustomerCode)

,TrxType={'sales'}

,YEAR=,MONTH=,MONTH_NUM=,MONTH_YEAR=,DAY=

,TrxDate={'<=$(vEOM_LastMonth)'}

}TrxDate

)

everything is fine so far

but what I want now is to calculate the amount of the transactions of these customers in the max invoice date

I think I have problem in the double quotation in the part where I wrote CusNum = {"=Sum({1<TrxType={'Sales'},TrxDate = P(TrxDate),Customer_Default_Salesman2 = P(Customer_Default_Salesman2)>}

Please advise

Re: Help in set analysis

ali_hijazi — Mon, 21 Nov 2016 10:04:53 GMT

Actually I tried the following expression to calculate the amount of the invoices whose date is max date of the not-served-customers but I get nulls

suM(

{

CustomerCode = E(

TrxType={'Sales'}

,TrxDate = P(TrxDate)

,Customer_Default_Salesman2 = P(Customer_Default_Salesman2)

,CusNum = {"=Sum({1<TrxType={'Sales'},TrxDate = P(TrxDate),Customer_Default_Salesman2 = P(Customer_Default_Salesman2)>} ValueUSD)>0"}

} CustomerCode

)

,TrxType={'sales'}

,YEAR=,MONTH=,MONTH_NUM=,MONTH_YEAR=,DAY=

,TrxDate={"=TrxDate=max(

{

CustomerCode = E(

TrxType={'Sales'}

,TrxDate = P(TrxDate)

,Customer_Default_Salesman2 = P(Customer_Default_Salesman2)

,CusNum = {'=Sum({1<TrxType={'Sales'},TrxDate = P(TrxDate),Customer_Default_Salesman2 = P(Customer_Default_Salesman2)>} ValueUSD)>0'}

} CustomerCode)

,TrxType={'sales'}

,YEAR=,MONTH=,MONTH_NUM=,MONTH_YEAR=,DAY=

,TrxDate={'<=$(vEOM_LastMonth)'}

}TrxDate

)

}if(vSelectedCurrency='USD',ValueUSD,ValueLBP))

topic Re: Help in set analysis in QlikView

Help in set analysis

Re: Help in set analysis