4 Replies Latest reply: Aug 28, 2014 12:43 PM by Evan Kurowski RSS

    setanalysis , avg and AGGR in a single expression

    Mark Munich

      Hello guys I want to solve the following Problem, can someone please help me out.

       

       

      AGGR(Max(Date(Date#(ENDDATE,'YYYYMMDD'),'DD/MM/YYYY') ) - Min(Date(Date#(STARTDATE,'YYYYMMDD'),'DD/MM/YYYY') ), PROGRAM))/count(DISTINCT PROGRAM)

       

      how to solve this :

       

      First I am calculating the Max of ENDDATE

       

      Then Min of STARTDATE

       

       

       

      and Then i Need to find the AVG(MAX(ENDDATE)-MIN(STARTDATE))

       

      and then I Need to Calculate this avg per program : this is something like static values AVG(MAX(ENDDATE)-MIN(STARTDATE)) for PROGRAM A, AVG(MAX(ENDDATE)-MIN(STARTDATE)) for PROGRAM B etc.,

       

      My calculation is getting me 90% of result when i compare with original data.

       

      Can i use Setanalysis, AVG and AGGR in a single Expression.

       

      The challenging thing is the dimension iam using in AGGR and SETANALYSIS would be the same.

       

       

      Thanks

       

      Mark

       

       

       

       

       

       

        • Re: setanalysis , avg and AGGR in a single expression
          Manish Kachhia

          Provide sample data please..

           

          or instead of

          count(DISTINCT PROGRAM)

          use

          Aggr(count(PROGRAM),PROGRAM)

          • Re: setanalysis , avg and AGGR in a single expression
            Evan Kurowski

            Hello marks,

             

            Not sure if this will apply to your situation exactly, but here's an illustration of what can happen with the Avg(Aggr()) combination that can cause unexpected results (and how the impacts may come from the data model).

             

            If you set up Avg(Aggr()) expressions against a single table or a table with perfect keys, the calculations should yield expected results.  And sometimes initial testing is done against a single table or a table with a perfect key, and it seems everything is working right.  Then perhaps only later, if the perfect key of that table somehow changes to an imperfect key, calculations may be impacted in ways that might not be expected.

             

            Once the fields involved in the Avg(Aggr()) calculation are joined to other tables with a non-Perfect key, the introduction of an entry for <NULL> potentially joins the denominator value list.  It also depends on whether the actual field in the calculation is the keyfield and whether selections are applied or not.

            • Perfect key - <NULL> NOT introduced in denominator
            • Imperfect key - no selections applied -  <NULL> introduced in denominator
            • Imperfect key - selections in filtering field applied (key or non-key)

                                   - all selections have entry in calculation field - <NULL> NOT introduced in denominator

                                   - any selection has a null value in calculation field - <NULL> introduced in denominator

             

            I know these scenarios may seem complicated, but identifying them has helped me manage the Avg(Aggr()) combinations, and maybe this will help you.

            20140827_avg_and_aggr_illustration.png