Forum Discussion

nsadams87xx's avatar
nsadams87xx
Icon for Helper III rankHelper III
8 years ago

Date Hierarchy Displaying too much data

Hey Everyone,   I have a matrix that is showing a date hierarchy for previous budget vs actual data.  When I drill down to a month level, the matrix also shows months that haven't happened yet.  Is...
Need Help
nsadams87xx's avatar
nsadams87xx
Icon for Helper III rankHelper III
8 years ago

Removing that column is showing the matrix correctly now.  What if I aggregated my data by a month level rather than at a date level?

nsadams87xx's avatar
nsadams87xx
Icon for Helper III rankHelper III
8 years ago

Sorry forgot the calculation.

 

% Change from Budget = IF(

(SUM(em_encounter_fact_date_agg[Pt Volume])/ sum(em_encounter_fact_date_agg[adj_arrivals_budget]) - 1) = -1 && ([Pt Volume Change %] = 0) && [Staff Hours % Change] = 0, 0, SUM(em_encounter_fact_date_agg[Pt Volume])/ sum(em_encounter_fact_date_agg[adj_arrivals_budget]) - 1

)

 

I have the IF because without it, the future months were showing as -100.00% and causing havoc with the Percentage Total.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi,

     

    Try this

     

    =IF(ISBLANK(SUM(em_encounter_fact_date_agg[Pt Volume])),BLANK(),your_formula_here)

  • nsadams87xx's avatar
    nsadams87xx
    Icon for Helper III rankHelper III
    8 years ago

    Unfortunately I got the same results.  The future months are still showing up.  I'm pretty convinced it's because of the tier structure of the dates.  My thinking is since year is being included in my hierarchy it's forcing itself to have to take the entire year into account including dragging the lower tiers with it when making the calculations with my measurement.  I think I'm going to put this one to bed for now.  Thank you everyone.

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    8 years ago

    % Change from Budget = IF(

    (SUM(em_encounter_fact_date_agg[Pt Volume])/ sum(em_encounter_fact_date_agg[adj_arrivals_budget]) - 1) = -1 && ([Pt Volume Change %] = 0) && [Staff Hours % Change] = 0, BLANK(), SUM(em_encounter_fact_date_agg[Pt Volume])/ sum(em_encounter_fact_date_agg[adj_arrivals_budget]) - 1

    )

     


    Try with this change, basically you are saying if you don't have any value then % will be blank. I assumed you assigned value to 0 to not to show -100%