Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Someone can help me
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)
Here is the example
Thanks for reply.
I am having some if condition in expression, its not working in the pick match function.
Thanks for reply.
I am having some if condition in expression, its not working in the pick match function.
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.
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((
(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=,[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((
(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))"} ,Year = {">=$(=$(vCurrent_Year))<=$(=_AMORTIZATION_YRS)"},[Report Section Code] = {'07 - End Point'}>}
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=,_CYCLE_PLAN_STAGE_PREVIOUS_ID =,[Snapshot Effective In Date]={"$(=GetFieldSelections(_ISSUES_RPT_DATE))"},Year = {">=$(=$(vCurrent_Year))<=$(=_AMORTIZATION_YRS)"},[Report Section Code] = {'12 - Volume'}>}
TOTAL <_BU_COST_ACCOUNT_SUMMARY_LEVEL_N,Year> _ABSORBED_COST_USD_A))
),$(vFormate)),
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))"},Year = {">=$(=$(vCurrent_Year))<=$(=_AMORTIZATION_YRS)"},[Report Section Code] = {'07 - End Point'}>}
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)
I am uploading sample application. Someone can help me pls.
can someone help me an above issues