1 Reply Latest reply on Aug 15, 2016 6:51 PM by Yuriy Fal

    Level of Detail calc based on user-defined parameters - taking too long

    Joe Milo

      Hi All- I'm having issues with a workbook taking too long to render; I think the issue is in a level of detail I'm trying to do in a calculated field. My workbook needs to get a flows field from one date and a balance field from another date. In the context of the below calc, if the field value from the below calc = 1, then I'm pulling the flow data. If the value from the calc = 2, then I pull the balance data. That functionality all works fine, I'm just trying to make this run faster. My data set only has 250K rows in it, but whenever a parameter is changed, it takes over 30 seconds to render. Given the volume, I was hoping for 3-4 seconds tops.

       

      Below is a portion of the calculated field:

      "Date Selection" is a parameter. 1 = Latest Date, 2 = Select Date. "2" allows the user to input a date into another parameter and the workbook looks at that date instead of the latest date. The file defaults to 1 and the latest date.

      "Time Period" is a parameter. 1 = Daily, 1.5 = Week To Date, 2= MTD, 3=QTD,4 = YTD.

      "Business Date" is just the date field in my data.

       

      if [Date Selection]=1 AND [Time Period]  =1 and [Business Date] = {FIXED:max([Business Date])}-0 then 1

      elseif [Date Selection]=1 AND [Time Period]  =1 and [Business Date] = {FIXED:max([Business Date])}-1 then 2

      //Weekly

      elseif [Date Selection]=1 AND [Time Period]  =1.5 and [Business Date] = {FIXED:max([Business Date])} then 1

      elseif [Date Selection]=1 AND [Time Period]  =1.5 and [Business Date] = datetrunc('week',{FIXED:max([Business Date])})-1 then 2

      //MTD,QTD,YTD

      elseif [Date Selection]=1 AND [Time Period]  =2 and [Business Date] = {FIXED:max([Business Date])} then 1

      elseif [Date Selection]=1 AND [Time Period]  =2 and [Business Date] = datetrunc('month',{FIXED:max([Business Date])})-1 then 2

      elseif [Date Selection]=1 AND [Time Period]  =3 and [Business Date] = {FIXED:max([Business Date])} then 1

      elseif [Date Selection]=1 AND [Time Period]  =3 and [Business Date] = datetrunc('quarter',{FIXED:max([Business Date])})-1 then 2

      elseif [Date Selection]=1 AND [Time Period]  =4 and [Business Date] = {FIXED:max([Business Date])} then 1

      elseif [Date Selection]=1 AND [Time Period]  =4 and [Business Date] = datetrunc('year',{FIXED:max([Business Date])})-1 then 2

       

      So the level of detail calc in the above snippet relies on the user-defined parameters. I think that's what is taking so long. I tried putting "{FIXED:max([Business Date])}" into its own calculated field and then using that calculated field in the above, but that only saved a few seconds.

       

      Is there a more efficient way to do this?

       

      Thanks,

      Joe

        • 1. Re: Level of Detail calc based on user-defined parameters - taking too long
          Yuriy Fal

          Hi Joe,

           

          Regarding performance:

           

          -- What is the DBMS under your datasource?

           

          -- Is it a chance that you're using data blending?

           

          Regarding the calc:

          You may speed up things this way:

           

          -- Create a String Parameter ( say [Period Parameter] )

          by choosing from the following List (omit quotes):

          'day' / 'week' / 'month' / 'quarter' / 'year'

           

          -- Then you could re-write your calculation like this:

          // Filter only the values of 1 and 2 to get the same results as in previous

           

          IF   [Date Selection] = 1

          THEN DATEDIFF( [Period Parameter], [Business Date], {MAX([Business Date])} ) + 1

          END

           

          As mentioned in the calc comment above, filtering the values equal 1 or 2

          would give the same results (I hope) as in your original calculation.

           

          Hope this could help. If not, the example workbook would be of a great use.

           

          Yours,

          Yuri