Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello got a pivot table in which I'm getting the latest invoice number and latest invoice date for customers not served in the selected month and year (check attached)
May you please help me get the amount of each invoice in the pivot table?
I couldn't get the sum of amount where InvoiceNum is the one gotten in the expression labeled Latest Invoice Num
Please advise
May be this:
Sum({<CustomerCode = E({<TrxType={'Sales'},CustomerInactive={'n'},CusNum = {"=Sum({<TrxType={'Sales'}>} ValueUSD)>0"}>} CustomerCode)
,TrxType={'sales'}
,CustomerInactive={'n'}
,YEAR=,MONTH=,MONTH_NUM=,MONTH_YEAR=,DAY=
,TrxDate={'<=$(vEOM_LastMonth)'}>}
Aggr(If(Only({1}InvoiceNum) = Max(TOTAL <Customer_Default_Salesman, Routing, CusName, CustomerCode> {<CustomerCode = E({<TrxType={'Sales'},CustomerInactive={'n'},CusNum = {"=Sum({<TrxType={'Sales'}>} ValueUSD)>0"}>} CustomerCode)
,TrxType={'sales'}
,CustomerInactive={'n'}
,YEAR=,MONTH=,MONTH_NUM=,MONTH_YEAR=,DAY=
,TrxDate={'<=$(vEOM_LastMonth)'}>}InvoiceNum),
Sum({<CustomerCode = E({<TrxType={'Sales'},CustomerInactive={'n'},CusNum = {"=Sum({<TrxType={'Sales'}>} ValueUSD)>0"}>} CustomerCode)
,TrxType={'sales'}
,CustomerInactive={'n'}
,YEAR=,MONTH=,MONTH_NUM=,MONTH_YEAR=,DAY=
,TrxDate={'<=$(vEOM_LastMonth)'}>}ValueUSD)), Customer_Default_Salesman, Routing, CusName, CustomerCode, InvoiceNum))
What is your amount field?
ValueUSD
May be this:
Sum({<CustomerCode = E({<TrxType={'Sales'},CustomerInactive={'n'},CusNum = {"=Sum({<TrxType={'Sales'}>} ValueUSD)>0"}>} CustomerCode)
,TrxType={'sales'}
,CustomerInactive={'n'}
,YEAR=,MONTH=,MONTH_NUM=,MONTH_YEAR=,DAY=
,TrxDate={'<=$(vEOM_LastMonth)'}>}
Aggr(If(Only({1}InvoiceNum) = Max(TOTAL <Customer_Default_Salesman, Routing, CusName, CustomerCode> {<CustomerCode = E({<TrxType={'Sales'},CustomerInactive={'n'},CusNum = {"=Sum({<TrxType={'Sales'}>} ValueUSD)>0"}>} CustomerCode)
,TrxType={'sales'}
,CustomerInactive={'n'}
,YEAR=,MONTH=,MONTH_NUM=,MONTH_YEAR=,DAY=
,TrxDate={'<=$(vEOM_LastMonth)'}>}InvoiceNum),
Sum({<CustomerCode = E({<TrxType={'Sales'},CustomerInactive={'n'},CusNum = {"=Sum({<TrxType={'Sales'}>} ValueUSD)>0"}>} CustomerCode)
,TrxType={'sales'}
,CustomerInactive={'n'}
,YEAR=,MONTH=,MONTH_NUM=,MONTH_YEAR=,DAY=
,TrxDate={'<=$(vEOM_LastMonth)'}>}ValueUSD)), Customer_Default_Salesman, Routing, CusName, CustomerCode, InvoiceNum))
Structure would be like:
Sum( <Set> If( InvoiceNum=Aggr( <expression that generates the invoice>, dim1, dim2..), Amount)
Sum({<CustomerCode = E({<TrxType={'Sales'},CustomerInactive={'n'},CusNum = {"=Sum({<TrxType= 'Sales'}>} ValueUSD)>0"}>} CustomerCode),TrxType={'sales'},CustomerInactive={'n'} ,YEAR=,MONTH=,MONTH_NUM=,MONTH_YEAR=,DAY=,TrxDate={'<=$(vEOM_LastMonth)'}>}
If(InvoiceNum=Aggr(NODISTINCT max({<CustomerCode = E({<TrxType={'Sales'},CustomerInactive={'n'}
,CusNum = {"=Sum({<TrxType={'Sales'}>} ValueUSD)>0"}>}CustomerCode),TrxType={'sales'} ,CustomerInactive={'n'},YEAR=,MONTH=,MONTH_NUM=,MONTH_YEAR=,DAY=,TrxDate={'<=$(vEOM_LastMonth)'}
>}InvoiceNum)
,Customer_Default_Salesman, Routing, CusName, CustomerCode ), ValueUSD))
thank you very much indeed
I tried with FirstSortedValue() function also, but somehow it was not working.