topic Re: Aggregating by filtered Textbox in QlikView

Aggregating by filtered Textbox

Tue, 31 Jul 2012 20:17:08 GMT

Hello All,

Below is the formula for my calculated field in the QV Pivot that aggregates sum by WEEK_NUM, how can I aggregate the sum by what I select in the WEEK_NUM listbox ? That is if the weeks selected in WEEK_NUM list box show up in the WEEK_NUM=()

Sum

({$<QUARTER={'SEP-11Q'},WEEK_NUM={1,2,3,4,5,6,7,8,9,10,11,12,13}>} MGMT_DIRECT_PLAN)

Thank you,

Michael.

Re: Aggregating by filtered Textbox

Tue, 31 Jul 2012 22:53:58 GMT

If the expression needs to repond to the selections, please remove WEEK_NUM from set analysis. I am not sure if I understand you correctly.

However you can try adding the following expression instead of hardcoding the numbers:

WEEK_NUM = {"$(=Concat(WEEK_NUM,','))"}

Kiran.

Re: Aggregating by filtered Textbox

Wed, 01 Aug 2012 18:29:50 GMT

Kiran, Thank you for the response !

MGMT_DIRECT_PLAN - is the amount, I am trying to aggregate by week of the quarter. So, WEEK_NUM is a dimension, with members 1 through 13. I have a WEEK_NUM listbox in which I select weeks and as a result i have the sum of MGMT_DIRECT_PLAN by selected weeks. I have variable Total =SUM ({$<QUARTER={'SEP-11Q'},WEEK_NUM={1,2,3,4,5,6,7,8,9,10,11,12,13}>} MGMT_DIRECT_PLAN)

that gives me a total of weeks 1 through 13 regardless of what i have selected in teh WEEK_NUM textbox.

What I am building is a calculated field, amount of through selected week as a percentage of total

.So what I need to build is something like this

SUM ({$<QUARTER={'SEP-11Q'},WEEK_NUM={1,2,3,4,5,6,7}>} MGMT_DIRECT_PLAN) /

SUM ({$<QUARTER={'SEP-11Q'},WEEK_NUM={1,2,3,4,5,6,7,8,9,10,11,12,13}>} MGMT_DIRECT_PLAN)

But I would prefer the nominator to be a live variable, that is to respond what I select in the listbox WEEK_NUM

Thank you again !

Re: Aggregating by filtered Textbox

Wed, 01 Aug 2012 19:02:51 GMT

Kiran, Thank you for the response ! MGMT_DIRECT_PLAN - is the amount, I am trying to aggregate by week of the quarter. So, WEEK_NUM is a dimension, with members 1 through 13. I have a WEEK_NUM listbox in which I select weeks and as a result i have the sum of MGMT_DIRECT_PLAN by selected weeks. I have variable Total =SUM ({$} MGMT_DIRECT_PLAN) that gives me a total of weeks 1 through 13 regardless of what i have selected in teh WEEK_NUM textbox. What I am building is a calculated field, amount of through selected week as a percentage of total .So what I need to build is something like this SUM ({$} MGMT_DIRECT_PLAN) / SUM ({$} MGMT_DIRECT_PLAN) But I would prefer the numerator to be a live variable, that is to respond what I select in the listbox WEEK_NUM Thank you again !