7 Replies Latest reply: Aug 23, 2018 9:25 AM by Mahitha M RSS

    Small correction help needed in the expression

    Mahitha M

      Hi Experts,

       

      I have used below expression in the table for calculating the percentage.

       

      The manual calculation for the percentage is

      Each Report Date wise each name wise individual Scope Sum(CC1)/Each Report Date wise each name wise Total Sum(CC1)



      By the below expression I am getting the Total at Name level. Please help me to get the Total at Each Report date wise Individual Name Total.

       

       

      Please find the below sample app.

       

      Sum(Aggr(RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),
      Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1,
      [Reporting Date],Date, Name, SCOPE))


      /


      Sum(TOTAL <Name> {<SCOPE>} Aggr(RangeMax(0,Sum({<SCOPE>}Value)) +
      RangeMax(Sum({<SCOPE>}Item1),Sum({<SCOPE>}Item2),Sum({<SCOPE>}Item3),Sum({<SCOPE>}Item4),
      Sum({<SCOPE>}Item5),Sum({<SCOPE>}Item6),Sum({<SCOPE>}Item7),Sum({<SCOPE>}Item8),Sum({<SCOPE>}Item9),
      Sum({<SCOPE>}Item10),Sum({<SCOPE>}Item11))/5+0.1, Date, Name, SCOPE))

      Percentage.png

        • Re: Small correction help needed in the expression
          Sunny Talwar

          What do you want to get? It seems you want 69% and 31% and you are getting those numbers?

            • Re: Small correction help needed in the expression
              Mahitha M

              Hi stalwar1,

               

              After selecting Reporting Date and Name=aim1 I am getting the correct percentage.

              But by default need to show the percentage in the report.

               

              Please help me on this.

              Thanks in advance.

                • Re: Small correction help needed in the expression
                  Mahitha M

                  In the app there is number of dates. Other Dates also contains the same Name as aim 1 in the Percentage need to divide only 18.08.2018 report date aim1 total.

                  • Re: Small correction help needed in the expression
                    Sunny Talwar

                    This

                     

                    Sum(Aggr(RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),

                    Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1,

                    [Reporting Date],Date, Name, SCOPE))

                    /

                    Sum(TOTAL <Name, [Reporting Date]> {<SCOPE>} Aggr(RangeMax(0,Sum({<SCOPE>}Value)) +

                    RangeMax(Sum({<SCOPE>}Item1),Sum({<SCOPE>}Item2),Sum({<SCOPE>}Item3),Sum({<SCOPE>}Item4),

                    Sum({<SCOPE>}Item5),Sum({<SCOPE>}Item6),Sum({<SCOPE>}Item7),Sum({<SCOPE>}Item8),Sum({<SCOPE>}Item9),

                    Sum({<SCOPE>}Item10),Sum({<SCOPE>}Item11))/5+0.1, Date, Name, SCOPE, [Reporting Date]))

                      • Re: Small correction help needed in the expression
                        Mahitha M

                        Hi stalwar1,

                         

                        Thanks for your reply. The percentage is getting correct. Here I am getting one more issue.

                        I have to multiple this percentage with 18.08.2018 > aim1> Individual Scope Amounts.

                        Here getting wrong amounts.

                        I have used below expression. Could you please help me on this.

                         

                        =Sum(Amount)
                        *

                        (Sum(Aggr(RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),
                        Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1,
                        [Reporting Date],Date, Name, SCOPE))
                        /
                        Sum(TOTAL <Name, [Reporting Date]> {<SCOPE>} Aggr(RangeMax(0,Sum({<SCOPE>}Value)) +
                        RangeMax(Sum({<SCOPE>}Item1),Sum({<SCOPE>}Item2),Sum({<SCOPE>}Item3),Sum({<SCOPE>}Item4),
                        Sum({<SCOPE>}Item5),Sum({<SCOPE>}Item6),Sum({<SCOPE>}Item7),Sum({<SCOPE>}Item8),Sum({<SCOPE>}Item9),
                        Sum({<SCOPE>}Item10),Sum({<SCOPE>}Item11))/5+0.1, Date, Name, SCOPE, [Reporting Date]))
                        )

                        Amount.png

                          • Re: Small correction help needed in the expression
                            Sunny Talwar

                            I have no clue what you mean? We are not even using Amount, what is that expression for? Is this another issue?

                              • Re: Small correction help needed in the expression
                                Mahitha M

                                Hi stalwar1,

                                 

                                I have one more requirement in the same expression.

                                I have applied default selection extension  in the app with Max([Reporting Date])

                                So  I am getting only 19.08.2018 data in the table by default.

                                 

                                Could you please help me to add [Reporting Date]= in the below expression for avoiding default Max([Reporting Date]) selection from extension.

                                 

                                 

                                =Sum(Amount)

                                *

                                (Sum(Aggr(RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),
                                Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1,
                                [Reporting Date],Date, Name, SCOPE))


                                /


                                Sum(TOTAL <Name, [Reporting Date]> {<SCOPE>} Aggr(RangeMax(0,Sum({<SCOPE>}Value)) +
                                RangeMax(Sum({<SCOPE>}Item1),Sum({<SCOPE>}Item2),Sum({<SCOPE>}Item3),Sum({<SCOPE>}Item4),
                                Sum({<SCOPE>}Item5),Sum({<SCOPE>}Item6),Sum({<SCOPE>}Item7),Sum({<SCOPE>}Item8),Sum({<SCOPE>}Item9),
                                Sum({<SCOPE>}Item10),Sum({<SCOPE>}Item11))/5+0.1, Date, Name, SCOPE, [Reporting Date]))
                                )

                                 

                                Thanks in advance.