Display Total

santharubban — Fri, 04 Nov 2016 15:22:33 GMT

Hi All,

I have requirement, in pivot table to display 6 yrs cost but total should add only 5 yrs cost. I have attached a sample output.

Re: Display Total

santharubban — Fri, 04 Nov 2016 15:47:20 GMT

Someone can help me

Re: Display Total

neelamsaroha157 — Fri, 04 Nov 2016 15:51:18 GMT

You can create a synthetic dimension with Pick & match

=Pick(match(Dim, 'Calculation', 'Totals'),Year, 'Totals')

where Dim is a column created in script as inline which has two value Calculation, Totals

Or you can use valueList to create the synthetic dimension.

then use pick/match in expression

Pick(Match(Dim,'Calculation', 'Totals'),

Expression,

ExpressionForTotal)

Re: Display Total

neelamsaroha157 — Fri, 04 Nov 2016 15:59:56 GMT

Here is the example

Re: Display Total

santharubban — Sat, 05 Nov 2016 22:07:18 GMT

Thanks for reply.

I am having some if condition in expression, its not working in the pick match function.

Re: Display Total

santharubban — Sat, 05 Nov 2016 22:07:49 GMT

Thanks for reply.

I am having some if condition in expression, its not working in the pick match function.

Re: Display Total

Gysbert_Wassenaar — Sun, 06 Nov 2016 14:51:38 GMT

Create a new small inline table to create a new field to use a dimension:

Periods:

LOAD * INLINE [

Year, ReportYear

2016, 2016

2017, 2017

2018, 2018

2019, 2019

2020, 2020

2017-2020 Total, 2017

2017-2020 Total, 2018

2017-2020 Total, 2019

2017-2020 Total, 2020

];

Next reload the document and replace the Year dimension in your pivot table with the new ReportYear field.

Re: Display Total

santharubban — Tue, 22 Nov 2016 15:35:54 GMT

Hi All,

Thanks for idea, Its worked.

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: Display Total

santharubban — Tue, 22 Nov 2016 15:59:16 GMT

I am uploading sample application. Someone can help me pls.

Re: Display Total

santharubban — Tue, 22 Nov 2016 16:37:19 GMT

can someone help me an above issues

topic Re: Display Total in QlikView

Display Total

Re: Display Total

Re: Display Total

Re: Display Total

Re: Display Total

Re: Display Total

Re: Display Total

Re: Display Total

Re: Display Total

Re: Display Total