topic Re: Trying to create a listbox with values created from expressions in QlikView

Trying to create a listbox with values created from expressions

Tue, 20 Jun 2017 17:55:29 GMT

I am trying to create a listbox to use as a filter for a pivot table that I have, the problem is that the displayed columns in that pivot table are all created by expressions, is there a way I can have the listbox show these choices?

Re: Trying to create a listbox with values created from expressions

Anonymous — Tue, 20 Jun 2017 19:00:49 GMT

if you want to filter the expression result in your pivot table, try:

aggr(YOURPIVOTABLE_EXPRESSION,YOURPIVOTABLE_DIMENSION_1, ... YOURPIVOTABLE_DIMENSION_N)

A sample would help...

Re: Trying to create a listbox with values created from expressions

Tue, 20 Jun 2017 19:16:29 GMT

well, right now I have a table with 3 different calculated columns, so 3 different expressions. the pivot table is using 3 dimensions, expense_type, Line_type, Line_subtype. each of the 3 expressions are almost identical. here is one of them for an example:

(sum({< SOURCE_TYPE = {'ACTUAL'},MONTH = {"$(=MONTHSTART(today()-1,-1))"},EXPENSE_TYPE-={'Base Headcount','Contractors','HR Physical Headcount'} >}AMOUNT)

sum({< SOURCE_TYPE = {'PLAN'},MONTH = {"$(=MONTHSTART(today()-1,-1))"},EXPENSE_TYPE-={'Base Headcount','Contractors','HR Physical Headcount'} >}AMOUNT))

-1

any help is appreciated

Re: Trying to create a listbox with values created from expressions

Anonymous — Tue, 20 Jun 2017 19:22:38 GMT

try

aggr(

(sum({< SOURCE_TYPE = {'ACTUAL'},MONTH = {"$(=MONTHSTART(today()-1,-1))"},EXPENSE_TYPE-={'Base Headcount','Contractors','HR Physical Headcount'} >}AMOUNT)

sum({< SOURCE_TYPE = {'PLAN'},MONTH = {"$(=MONTHSTART(today()-1,-1))"},EXPENSE_TYPE-={'Base Headcount','Contractors','HR Physical Headcount'} >}AMOUNT))

-1

expense_type, Line_type, Line_subtype

)

as Listbox expression:

Re: Trying to create a listbox with values created from expressions

Tue, 20 Jun 2017 20:12:05 GMT

thanks for the help, I ended up just using an in line load to fix the issue.