Help in expression

[ali_hijazi]

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

[tresesco]

What is your amount field?

[ali_hijazi]

ValueUSD

[sunny_talwar · Accepted solution]

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

[tresesco]

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

[ali_hijazi]

thank you very much indeed

[sunny_talwar]

I tried with FirstSortedValue() function also, but somehow it was not working.