Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all
I have a year button, period column with 1,2,3,4,5...13 values in it and a chart with 2 dimensions and three value list based columns those are 1) PERIOD, 2) YEAR TO DATE, 3) FULL YEAR.
now my requirement is :
if I click on Year to date button, the selections in the chart should be like for the values in the PERIOD column of the chart should show period list box 11 value and at the same time YEAR TO DATE and FULL YEAR values should be equal to period list box 1 - 10 values data.
more clear : the button should select two different values in same list box for two different columns
for PERIOD Column: period list box slection in only 11 value
YEAR TO DATE and FULL YEAR: period list box slections of 1 - 10 ;
these selections should happen at a same time when user clicks on button.
please suggest me
Not sure about your requirement, how with in single list box you can select 10 & 11? You can select either 10 or 11.
for PERIOD Column: period list box slection in only 11 value
YEAR TO DATE and FULL YEAR: period list box slections of 1 - 10 ;
these selections should happen at a same time when user clicks on button.
Hi John,
Based on my Understanding, you can try to change your expression for "PERIOD" data.. Like
$(=vMTDMonth+1)
or you can try to change your Command Button (YearToDate) actions for Period Field.. Like
= '('&Concat(If(Period<=11 and Period >0,Period), '|')&')'
Then you try to change your YEAR TO DATE and FULL YEAR to decrease as '-1'. Like
Let us know, if this not your need.
Hello Jagan
thanks for the reply..
if I click on button 1,2,3...10 values should select on period and 11 value should only to period column
like sample syntax in button:
if(column name = 'PERIOD', period =11, period >0 and period<=10)
but I need to write this logic
Hello settu,
thanks a lot for the reply.
could you please share the sample app, because I was little confused of what you said.
please share the app
Hi,
Try this expression
=if([P&L ACCOUNT] <> null() and [P&L Level] <> 's'
and valuelist('PERIOD', 'YEAR TO DATE','FULL YEAR')= 'PERIOD',
if([P&L GROUP]='EBITDA',(if(IsNum(Period)=0,sum({<Period=, Periodtest={'<=$(=vMTDMonth + 1)'},Accounttest={'PL_EBIT100'}>}Actualtest),sum({<Period=, Periodtest={'$(vMTDMonth + 1)'},Accounttest={'PL_EBIT100'}>}Actualtest))
-if(IsNum(Period)=0,sum({<Period=, Periodtest={'<=$(=vMTDMonth + 1)'},Accounttest={'PL_223501'}>}Actualtest),sum({<Period=, Periodtest={'$(vMTDMonth + 1)'},Accounttest={'PL_223501'}>}Actualtest))
-if(IsNum(Period)=0,sum({<Period=, Periodtest={'<=$(=vMTDMonth + 1)'},Accounttest={'PL_320006'}>}Actualtest),sum({<Period=, Periodtest={'$(vMTDMonth + 1)'},Accounttest={'PL_320006'}>}Actualtest))
-if(IsNum(Period)=0,sum({<Period=, Periodtest={'<=$(=vMTDMonth + 1)'},Accounttest={'PL_330006'}>}Actualtest),sum({<Period=, Periodtest={'$(vMTDMonth + 1)'},Accounttest={'PL_330006'}>}Actualtest))),
if([P&L GROUP]='EBITDOL',(if(IsNum(Period)=0,sum({<Period=, Periodtest={'<=$(=vMTDMonth + 1)'},Accounttest={'PL_EBIT100'}>}Actualtest),sum({<Period=, Periodtest={'$(vMTDMonth + 1)'},Accounttest={'PL_EBIT100'}>}Actualtest))
-if(IsNum(Period)=0,sum({<Period=, Periodtest={'<=$(=vMTDMonth + 1)'},Accounttest={'PL_223501'}>}Actualtest),sum({<Period=, Periodtest={'$(vMTDMonth + 1)'},Accounttest={'PL_223501'}>}Actualtest))
-if(IsNum(Period)=0,sum({<Period=, Periodtest={'<=$(=vMTDMonth + 1)'},Accounttest={'PL_320006'}>}Actualtest),sum({<Period=, Periodtest={'$(vMTDMonth + 1)'},Accounttest={'PL_320006'}>}Actualtest))
-if(IsNum(Period)=0,sum({<Period=, Periodtest={'<=$(=vMTDMonth + 1)'},Accounttest={'PL_330006'}>}Actualtest),sum({<Period=, Periodtest={'$(vMTDMonth + 1)'},Accounttest={'PL_330006'}>}Actualtest))
-if(IsNum(Period)=0,sum({<Period=, Periodtest={'<=$(=vMTDMonth + 1)'},Accounttest={'PL_223503'}>}Actualtest),sum({<Period=, Periodtest={'$(vMTDMonth + 1)'},Accounttest={'PL_223503'}>}Actualtest))),
sum({<Period=, month ={$(vMTDPeriod)}>}Actual))),
if([P&L ACCOUNT] <> null() and [P&L Level] <> 's'and valuelist('PERIOD', 'YEAR TO DATE','FULL YEAR')= 'YEAR TO DATE',
if([P&L GROUP]='EBITDA',(if(IsNum(Period)=0,sum({1<Periodtest={"<=$(=vPTDPeriod1)"},Accounttest={'PL_EBIT100'}>}Actualtest),sum({1<Periodtest={"<=$(=vPTDPeriod1)"},Accounttest={'PL_EBIT100'}>}Actualtest))
-if(IsNum(Period)=0,sum({1<Periodtest={"<=$(=vPTDPeriod1)"},Accounttest={'PL_223501'}>}Actualtest),sum({1<Periodtest={"<=$(=vPTDPeriod1)"},Accounttest={'PL_223501'}>}Actualtest))
-if(IsNum(Period)=0,sum({1<Periodtest={"<=$(=vPTDPeriod1)"},Accounttest={'PL_320006'}>}Actualtest),sum({1<Periodtest={"<=$(=vPTDPeriod1)"},Accounttest={'PL_320006'}>}Actualtest))
-if(IsNum(Period)=0,sum({1<Periodtest={"<=$(=vPTDPeriod1)"},Accounttest={'PL_330006'}>}Actualtest),sum({1<Periodtest={"<=$(=vPTDPeriod1)"},Accounttest={'PL_330006'}>}Actualtest))),
if([P&L GROUP]='EBITDOL',(if(IsNum(Period)=0,sum({1<Periodtest={"<=$(=vPTDPeriod1)"},Accounttest={'PL_EBIT100'}>}Actualtest),sum({1<Periodtest={"<=$(=vPTDPeriod1)"},Accounttest={'PL_EBIT100'}>}Actualtest))
-if(IsNum(Period)=0,sum({1<Periodtest={"<=$(=vPTDPeriod1)"},Accounttest={'PL_223501'}>}Actualtest),sum({1<Periodtest={"<=$(=vPTDPeriod1)"},Accounttest={'PL_223501'}>}Actualtest))
-if(IsNum(Period)=0,sum({1<Periodtest={"<=$(=vPTDPeriod1)"},Accounttest={'PL_320006'}>}Actualtest),sum({1<Periodtest={"<=$(=vPTDPeriod1)"},Accounttest={'PL_320006'}>}Actualtest))
-if(IsNum(Period)=0,sum({1<Periodtest={"<=$(=vPTDPeriod1)"},Accounttest={'PL_330006'}>}Actualtest),sum({1<Periodtest={"<=$(=vPTDPeriod1)"},Accounttest={'PL_330006'}>}Actualtest))
-if(IsNum(Period)=0,sum({1<Periodtest={"<=$(=vPTDPeriod1)"},Accounttest={'PL_223503'}>}Actualtest),sum({1<Periodtest={"<=$(=vPTDPeriod1)"},Accounttest={'PL_223503'}>}Actualtest))),
sum({1<Period ={"<=$(=vPTDPeriod1)"}>}Actual))),''))
Regards,
Jagan.
Hi,
you can try the Jagan's Exprssion.
Wherever you have the PERIOD with valuelist, you need to put Periodtest={'<=$(=vMTDMonth + 1)'}
for reference, check the attachement..