Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am currently using a Vizlib Finance report to create a P&L. We have grouped the measures for MTD & YTD using this formula so that we only have to create 7 columns and not 14 individual columns which creates a strain on calculations and the system. The below formula is in my Dimensions
=if([Period Category]='MTD' or [Period Category]='YTD',[Period Category])
As an example, the following is part of my measure calculation for my measure
Pick([$(=only(%Acct_Report))],
//Calc 1 in excel file
Num(Sum({$<SCENARIO_ANCESTOR_ID = {$(=vActual)},LOB_ANCESTOR_ID={[$(=only(%Acct_Report_LOB))]},REFERENCE_PERIOD = {'SEQ MTD','PY YTD'}>}AMOUNT)/1000,[$(=only(%Acct_Report_formatcode))]),
//2
' ',
//3
Num(
(Sum({$<SCENARIO_ANCESTOR_ID={$(=vActual)},LOB_ANCESTOR_ID={[$(=only(%Acct_Report_LOB))]},ACCOUNT_ANCESTOR_ID = {'4000'},REFERENCE_PERIOD = {'SEQ MTD','PY YTD'}>}AMOUNT)
-Sum({$<SCENARIO_ANCESTOR_ID = {$(=vActual)},LOB_ANCESTOR_ID={[$(=only(%Acct_Report_LOB))]},ACCOUNT_ANCESTOR_ID = {'IS3','67000'},REFERENCE_PERIOD = {'SEQ MTD','PY YTD'}>}AMOUNT))/1000
,[$(=only(%Acct_Report_formatcode))]),
As you can see in my Reference_Period I am selecting either the sequential mtd or the prior ytd. What is happening is that the calculation is (for example I select Feb 2021 as the period) the Pick calc is giving me January 2021 data, is there a way to hide it using the Calc. Condition expression?
I only want it to show for MTD but not YTD. Essentially I want to show columns 1-7 under MTD, and 8,12-14 under YTD, but I don't want to create 11 measures.
Thank you,
Chris