1 Reply Latest reply on Jul 18, 2016 12:01 PM by Kenneth McBride

    Building a Dynamic Date Calculated Member

    Jennifer Dwyer

      Hi all –

       

      I am working on a dashboard that was built by a former team member with more coding experience than I have. I’ve been able to reverse engineer a lot of the work, but one of the calculated members is presenting a challenge. Right now we have a field called ‘Business Days in Year’ which is coded as below:

       

      count(Filter([Date].[Date].currentmember.siblings* strtomember('[Date].[Year].&['+ CurrentYear +'-01-01T00:00:00]'), [Date].[IsBusiness Day].currentmember.name = 'Y'))

       

      I’d like to come up with a more granular member that is ‘Business Days in Month’ so that we can look at a narrower range of performance data.  I tried to re-write it based on the prior code, but it doesn’t seem to be working. Below is what I have so far.  Tableau says the calculation is valid, but when I drag it into a sheet it appears blank.

       

      count(Filter([Date].[Date].currentmember.siblings* strtomember('[Date].[Year].&['+ CurrentYear +'-'+ CurrentMonth +'-01T00:00:00]'), [Date].[Is Business Day].currentmember.name = 'Y'))

       

      I can’t attach a workbook, but attached is a screenshot of our date hierarchy.  Thanks for your help!

        • 1. Re: Building a Dynamic Date Calculated Member
          Kenneth McBride

          Jennifer,

           

          I'll admit I don't work much with cubes so my experience with calculated members is limited but I would suggest that you try breaking that calculation into several pieces (and several different calculated members) and then drag each piece onto a sheet to see which one is returning the NULL. This might help you narrow down the part of the calculation that is the problem. Sorry I couldn't be of more assistance and good luck.

           

          Ken