topic Re: Expression reuse in QlikView

Expression reuse

ali_hijazi — Thu, 22 Dec 2016 12:06:37 GMT

Hello Got a pivot table showing AR Ageing (check attached) document

in the copy that I've attached I have the Invoice Number 84035 with amount 421,444.26 and No. of Days =3

then I got the remaining invoices with number of days = 2 and 1

What I want now is to create a new pivot table showing for each customer the amount of invoices whose ageing days is between 1, and 2

and those whose ageing is >2

i.e. the pivot table shows customer name, then expression 1 sum(amount) where ageing is between 1,and 2 and a second expression showing the amount where ageing is >2

i.e. for the available customer show:

421,444.26

and on the second column show

25029.54

of course by reusing the expression used to calculate ageing

Please advise

Re: Expression reuse

sunny_talwar — Thu, 22 Dec 2016 12:36:15 GMT

Is this what you want?

Re: Expression reuse

ali_hijazi — Thu, 22 Dec 2016 12:40:58 GMT

you didn't attach the solution

Re: Expression reuse

sunny_talwar — Thu, 22 Dec 2016 12:44:46 GMT

I have attached the qvw file above

Re: Expression reuse

vinieme12 — Thu, 22 Dec 2016 12:49:28 GMT

instead of Creating new tables , why not re-use the same table and add a Listbox for the user to filter on?

Create an expression listbox for users to filter on

=AGGR(

$(vCustomerAgeing_OpenInvoice_Days)

,InvoiceNum)

Re: Expression reuse

ali_hijazi — Thu, 22 Dec 2016 12:54:58 GMT

I don't want this as dimension

I want to create separate expressions

in fact on the real dashboard I want to have the classification as columns:

31-60 61-90 91-120 >120

or something like this

I asked for 1&2 and >2 to match the available data in the copy I shared with you

Re: Expression reuse

ali_hijazi — Thu, 22 Dec 2016 12:56:17 GMT

I want to have pre-defined brackets

<= 30 31-60 61-90 91-120 >120

Re: Expression reuse

vinieme12 — Thu, 22 Dec 2016 13:03:59 GMT

try Expression listbox as

= replace(AGGR(class($(vCustomerAgeing_OpenInvoice_Days),30),InvoiceNum),'<= x <',' - ')

Re: Expression reuse

ali_hijazi — Thu, 22 Dec 2016 13:04:47 GMT

and additional expressions may be added to the chart other than these required right now

Re: Expression reuse

vinieme12 — Thu, 22 Dec 2016 13:07:29 GMT

UPDATED EXPRESSION

= replace(AGGR(if($(vCustomerAgeing_OpenInvoice_Days)>120,'>120',class($(vCustomerAgeing_OpenInvoice_Days),30)),InvoiceNum),'<= x <',' - ')

Re: Expression reuse

ali_hijazi — Thu, 22 Dec 2016 13:15:14 GMT

this works if one expression is needed in the report

but I have other expressions to add like the current balance of the customer and I don't want to classify this

Re: Expression reuse

parimikittu — Thu, 22 Dec 2016 17:04:29 GMT

Please use interval match for your requirment. Below is the doc with examples.

Interval Match or Inline load

Catergorize our data based on ageing, then use it as dimension in your pivot table

Re: Expression reuse

ali_hijazi — Wed, 28 Dec 2016 14:09:21 GMT

this works if I have only one expression in the chart

but I got several expressions

Re: Expression reuse

joydipp1988 — Sat, 04 Feb 2017 16:35:19 GMT

Hi ali_hijazi,

So as per my understanding, there will be more than one expression under same bucket right ?

Do you want something like this-

Thanks,

Joy

Re: Expression reuse

ali_hijazi — Mon, 06 Feb 2017 14:19:32 GMT

yes