Set Analysis with Pick Match is not working

santharubban — Tue, 22 Nov 2016 21:02:45 GMT

Set analysis is not working as expected, Pick match is taking default date its not taking the selected date form _ISSUES_RPT_DATE date field.

Please find the expression below,

Pick(Match(_Dim,'_Calculation','_Totals')

if([Report Section Code] = '14 - Status of End point by Volume/Revenue',

if([Cost Account Summary (BoD)] ='Capital Spending',

(

Num(((sum({<[Snapshot Effective In Date]=,_ISSUES_RPT_DATE=,_ISS_BOD_DATA_MART_EFF_IN_Y=,_CYCLE_PLAN_STAGE_PREVIOUS_ID =,[Snapshot Effective In Date]={"$(=GetFieldSelections(_ISSUES_RPT_DATE))"},[Report Section Code] = {'07 - End Point'}>}

TOTAL <_BU_COST_ACCOUNT_SUMMARY_LEVEL_N,_SUMMARY_DISPLAY_YEAR> _ABSORBED_COST_USD_A))

(sum({<[Snapshot Effective In Date]=,_ISSUES_RPT_DATE=,_ISS_BOD_DATA_MART_EFF_IN_Y=,_CYCLE_PLAN_STAGE_PREVIOUS_ID =,[Snapshot Effective In Date]={"$(=GetFieldSelections(_ISSUES_RPT_DATE))"},[Report Section Code] = {'12 - Volume'}>}

TOTAL <_BU_COST_ACCOUNT_SUMMARY_LEVEL_N,_SUMMARY_DISPLAY_YEAR> _ABSORBED_COST_USD_A))

),$(vFormate))),

Num((

TOTAL <_BU_COST_ACCOUNT_SUMMARY_LEVEL_N,_SUMMARY_DISPLAY_YEAR> _ABSORBED_COST_USD_A))

(

sum({<[Snapshot Effective In Date]=,_ISSUES_RPT_DATE=,_ISS_BOD_DATA_MART_EFF_IN_Y=,[Snapshot Effective In Date]={"$(=GetFieldSelections(_ISSUES_RPT_DATE))"},[Report Section Code] = {'13 - Revenue'}>}

TOTAL <_BU_COST_ACCOUNT_SUMMARY_LEVEL_N,_SUMMARY_DISPLAY_YEAR> _ABSORBED_COST_USD_A))

), '#,##0.0%')

(If([Report Section Code] ='12 - Volume',Num(sum({<[Snapshot Effective In Date]=,_ISSUES_RPT_DATE=,_ISS_BOD_DATA_MART_EFF_IN_Y=,[Snapshot Effective In Date]={"$(=GetFieldSelections(_ISSUES_RPT_DATE))"}>}_ABSORBED_COST_USD_A)/1000,$(vFormate)),

(Num(sum({<[Snapshot Effective In Date]=,_ISSUES_RPT_DATE=,_ISS_BOD_DATA_MART_EFF_IN_Y=,[Snapshot Effective In Date]={"$(=GetFieldSelections(_ISSUES_RPT_DATE))"}>}_ABSORBED_COST_USD_A)/1000000,$(vFormate))))

))),

(

if([Report Section Code] = '14 - Status of End point by Volume/Revenue',

if([Cost Account Summary (BoD)] ='Capital Spending',

NUM((

TOTAL <_BU_COST_ACCOUNT_SUMMARY_LEVEL_N,Year> _ABSORBED_COST_USD_A))/

TOTAL <_BU_COST_ACCOUNT_SUMMARY_LEVEL_N,Year> _ABSORBED_COST_USD_A))

),$(vFormate)),

nUM((

TOTAL <_BU_COST_ACCOUNT_SUMMARY_LEVEL_N,Year> _ABSORBED_COST_USD_A))

(

sum({<[Snapshot Effective In Date]=,_ISSUES_RPT_DATE=,_ISS_BOD_DATA_MART_EFF_IN_Y=,[Snapshot Effective In Date]={"$(=GetFieldSelections(_ISSUES_RPT_DATE))"},Year = {">=$(=$(vCurrent_Year))<=$(=_AMORTIZATION_YRS)"},[Report Section Code] = {'13 - Revenue'}>}

TOTAL <_BU_COST_ACCOUNT_SUMMARY_LEVEL_N,Year> _ABSORBED_COST_USD_A))

), '#,##0.0%')

)

(If([Report Section Code] ='12 - Volume',Num(sum({<[Snapshot Effective In Date]=,_ISSUES_RPT_DATE=,_ISS_BOD_DATA_MART_EFF_IN_Y=,_CYCLE_PLAN_STAGE_PREVIOUS_ID =,Year = {">=$(=$(vCurrent_Year))<=$(=_AMORTIZATION_YRS)"},[Snapshot Effective In Date]={"$(=GetFieldSelections(_ISSUES_RPT_DATE))"}>}_ABSORBED_COST_USD_A)/1000,$(vFormate)),

(Num(sum({<[Snapshot Effective In Date]=,_ISSUES_RPT_DATE=,_ISS_BOD_DATA_MART_EFF_IN_Y=,_CYCLE_PLAN_STAGE_PREVIOUS_ID =,Year = {">=$(=$(vCurrent_Year))<=$(=_AMORTIZATION_YRS)"},[Snapshot Effective In Date]={"$(=GetFieldSelections(_ISSUES_RPT_DATE))"}>}_ABSORBED_COST_USD_A)/1000000,$(vFormate))))

))))

and one of the Dimension is below :

Pick(Match(_Dim,'_Calculation', '_Totals'), _SUMMARY_DISPLAY_YEAR, _DISPLAY_SUMMARY_TOTAL)

Re: Set Analysis with Pick Match is not working

marcus_sommer — Wed, 23 Nov 2016 12:19:03 GMT

I couldn't say what didn't worked with your expression but I suggest to simplify your expression-approach. To develop such expressions is really hard work but maintaining/debugging will be a nightmare.

- Marcus

Re: Set Analysis with Pick Match is not working

santharubban — Wed, 23 Nov 2016 14:09:26 GMT

Hi Marcus,

Thanks for suggestion and reply.

I have uploaded sample application can you please help.

Re: Set Analysis with Pick Match is not working

sunny_talwar — Wed, 23 Nov 2016 14:13:34 GMT

What is the expected output for the sample attached?

Re: Set Analysis with Pick Match is not working

santharubban — Wed, 23 Nov 2016 14:17:54 GMT

Hi Sunny,

Thanks for your reply.

Table1 need to display based on Date2 date and Table2 need to display based on vDate date.

Re: Set Analysis with Pick Match is not working

sunny_talwar — Wed, 23 Nov 2016 14:22:05 GMT

I sort of understand what you mean. But isn't this what is going on right now? If not what are the numbers you expect to see when you have 11/302016 selected in vDate and what do you expect to see when 11/28/2016 is selected in Date2?

Re: Set Analysis with Pick Match is not working

santharubban — Wed, 23 Nov 2016 14:38:10 GMT

Hi Sunny,

If i Select a date from vDate then Table 2 shouldn't reflect, only Table 1 should change based on the selection. But now

its not happen because of Pick Match function. Similarly if i select Date2 then table 2 should change not table1.

I hope you understand what i need.

Re: Set Analysis with Pick Match is not working

sunny_talwar — Wed, 23 Nov 2016 14:42:29 GMT

I don't see selection in vDate impacting your second table. But for the other way, I did see some weird behavior. See if the attached helps your resolve that issue

Pick(Match(Only({<vDate>}Dim),'Calculation','Totals'),

Sum({<vDate = p(Date2)>}Value),

Sum({<vDate = p(Date2),Year = {'2020','2019','2018','2017'}>}Value))

topic Set Analysis with Pick Match is not working in QlikView

Set Analysis with Pick Match is not working

Re: Set Analysis with Pick Match is not working

Re: Set Analysis with Pick Match is not working

Re: Set Analysis with Pick Match is not working

Re: Set Analysis with Pick Match is not working

Re: Set Analysis with Pick Match is not working

Re: Set Analysis with Pick Match is not working

Re: Set Analysis with Pick Match is not working