Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Chris_Elders_VZ
Contributor II
Contributor II

How to hide a measure based on Calc condition in a vizlib report

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?

 

Chris_Elders_VZ_1-1627922007609.png

 

 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.

Chris_Elders_VZ_2-1627922007650.png

 

Chris_Elders_VZ_3-1627922007675.png

Thank you,

Chris

 

0 Replies