1 Reply Latest reply on Jan 2, 2013 12:26 PM by Dan P.

    Calculation to Sort by Fiscal Year/Month using predefined fiscal start and end

    Ed Kukec

      Hi All,


      On my dashboard, users can view information aggregated by Year, Quarter or Month Depending on what they select as a parameter. Our Organization Fiscal Year Starts in April and ends on March 31st so I've managed to get a few calculated fields working to get the proper results that I wanted. If a record as a date = April 1st, 2002.....if they select "yearly" in the parameter they get 'FYE 2003'. If they select "quarterly" they get "2003 Q1". IF they select "Monthly" they get "2003 Apr". Whenever you create these calculated date fields, it reverts back to calendar year so I had to trick Tableau so to speak to get the results. This is what I did:


      Create Calculated field called: Discharge_dt_fyr           =    dateadd('month', 9, [DISCHARGE_DT])

      Create Calculated field called: Discharge_dt_month      =   dateadd('month', 12, [DISCHARGE_DT])


      Then I create another calculated field, called Time Dimension Calc that uses the two calculated fields above and the Time Dimension Parameter:


      IF [Time Dimension] = 'Yearly'     then 'FYE '+DATENAME('year',[Discharge_dt_fyr]) elseif

         [Time Dimension] = 'Quarterly' then DATENAME('year',[Discharge_dt_fyr])+" Q"+DATENAME('quarter',[Discharge_dt_fyr]) elseif

         [Time Dimension] = 'Monthly'  then DATENAME('year',[Discharge_dt_fyr])+" "+LEFT(DATENAME('month',[Discharge_dt_month]),3)



      This works fine and the counts and volumes are good. Here's the problem. The sorting. If I select month in the Time Dimension Parameter, it sorts in  alphabetic order so it goes 2003 April, 2003 Aug, 2003 Dec, 2003 Feb....Even Quarterly sorts are incorrect sometimes. So what I need is to be able to sort in the following order according to my Fiscal Year, Quarter or Month.


      If Yearly....FYE 2003, FYE 2004....

      If Quarterly....2003 Q1, 2003 Q2.....2003 Q4

      If Monthly...2003 Apr, 2003 May, 2003 Jun, 2003 Jul.....2003 Mar


      I tried converting the month to numbers and such but I can't seem to get it to work so any help would be appreciated. I think I'm close....it's probably easy but I can't figure it out.