Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
ali_hijazi
Partner
Partner

Help in expression

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

I can walk on water when it freezes
1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

View solution in original post

6 Replies
tresesco
MVP
MVP

What is your amount field?

ali_hijazi
Partner
Partner
Author

ValueUSD

I can walk on water when it freezes
sunny_talwar

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

Capture.PNG

View solution in original post

tresesco
MVP
MVP

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
Partner
Partner
Author

thank you very much indeed

I can walk on water when it freezes
sunny_talwar

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