Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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 !