Skip to main content
Announcements
Introducing a new Enhanced File Management feature in Qlik Cloud! GET THE DETAILS!
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