topic Re: button selection for value list based columns in QlikView

button selection for value list based columns

Anonymous — Tue, 03 Nov 2015 03:22:06 GMT

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

Re: button selection for value list based columns

jagan — Tue, 03 Nov 2015 04:00:10 GMT

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.

Re: button selection for value list based columns

settu_periasamy — Tue, 03 Nov 2015 04:08:56 GMT

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.

Re: button selection for value list based columns

Anonymous — Tue, 03 Nov 2015 04:14:32 GMT

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

Re: button selection for value list based columns

Anonymous — Tue, 03 Nov 2015 04:16:39 GMT

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

Re: button selection for value list based columns

jagan — Tue, 03 Nov 2015 04:20:17 GMT

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.

Re: button selection for value list based columns

settu_periasamy — Tue, 03 Nov 2015 05:00:11 GMT

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..