topic Re: Help in expression in QlikView

Help in expression

ali_hijazi — Tue, 29 Nov 2016 10:21:55 GMT

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

Re: Help in expression

tresB — Tue, 29 Nov 2016 10:35:20 GMT

What is your amount field?

Re: Help in expression

ali_hijazi — Tue, 29 Nov 2016 10:38:10 GMT

ValueUSD

Re: Help in expression

sunny_talwar — Tue, 29 Nov 2016 10:58:10 GMT

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

Re: Help in expression

tresB — Tue, 29 Nov 2016 11:50:12 GMT

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

Re: Help in expression

ali_hijazi — Tue, 29 Nov 2016 12:08:33 GMT

thank you very much indeed

Re: Help in expression

sunny_talwar — Tue, 29 Nov 2016 12:09:12 GMT

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