1 Reply Latest reply on Sep 7, 2018 2:43 PM by Hari Ankem

    Calculating stats based on 1 measure, complex LOD Help

    Eugene Trilesnik

      Hey guys, I am running into this problem and have no clue how to solve it. I need to do a WFTE / NFE for a specific selection. The problem that comes in is that NFE is calculated as a running sum. For example 1/1/16 if data is 5, and 9/1/16 data is 30, it means that the final NFE for the year is 30, not the sum of every number between 1/1/16 and 9/1/16. WHILE WFTE is individual. So 1/1/16 of 5 and 9/1/16 of 30, the final sum for the year would be 35.

       

      I want to take WFTE at 1/1/16 and divide it by 9/1/16 of NFE of a particular Program

      Then I want to take WFTE at 2/1/16 and divide it by 9/1/16 of NFE of a particular program

       

       

      Can anyone help me with the LOD I need for that? I tried:

       

      {INCLUDE [Dates]:

      SUM(IF [Accounts]="WFTE"

      THEN [Stats] END)}/ {INCLUDE [Dates]:

      SUM(IF [Accounts]="NFE"

      THEN [Stats] END)}

       

      It didn't work

       

      Best

        • 1. Re: Calculating stats based on 1 measure, complex LOD Help
          Hari Ankem

          Would this help?

          1.png

          Here is what I did:

          1. Created 2 parameters for Start and End Dates and assigned the values of 1/1/2016 and 9/1/2016 respectively.

           

          2. Added Date filter as shown below and added it to context. Added Accounts filter to exclude NFE but did not add this filter to context.

          1.png  1.png

           

          3. Created a calculated field named Stats Computed with the formula as shown below. This ensured that I have values for Stats for WFTE accounts too.

          1.png

           

          4. Next, created a calculated field to get me the divisor as shown below:

          1.png

           

          5. Finally, a calculated field for the WFTE Ratio as shown below:

          1.png

           

          A 2018.1 workbook is attached. Hope this helps.