3 Replies Latest reply: Aug 2, 2018 7:08 AM by Sunny Talwar RSS

    Complex Conditianal Sum with Max ,Agg  row fields

    Belguith Chachia

      Hello Everyone,

       

      I'm Trying  to Fetch a compleat Row From Data

       

      | CA  | Month_ca  | Year_ca |

        52,5        6              2018

        44,4        7              2018

        25,6        4              2017

        ....

       

      I was able to do This to fetch the hole row data

       

      Record fetchin with Month and year.

       

      Expression  =       Max(aggr(CA_Month,Year_CA,Month_CA))

       

      Label =FirstSortedValue( aggr(Year_CA,Month_CA,Year_CA), aggr(-Max(CA_Month),Year_CA,Month_CA) )

      & ' - '&

      FirstSortedValue( aggr(Month_CA,Year_CA,Month_CA), aggr(-Max(CA_Month),Month_CA,Month_CA) )

       

      The Result is A Kpi Indicator that shows  :     

                              2018 - 7

                                  52.5

      How ever I need to retrive another Data  basicly a Sum of Sales for the same Year_CA, and Month_CA

      So This is what i did  and it Does not Work

       

      Expression =

      Max(aggr(Max(CA_Month),Month_CA,Year_CA)) -

      Sum({

      <

      [Date.autoCalendar.Year]  = {${FirstSortedValue( aggr(Year_CA,Month_CA,Year_CA), aggr(-Max(CA_Month),Year_CA,Month_CA) )}} ,

      [Date.autoCalendar.Month] = {${FirstSortedValue( aggr(Month_CA,Year_CA,Month_CA), aggr(-Max(CA_Month),Month_CA,Month_CA) )}} ,

      [ODS_GLOBAL.Type de pièce] ={'Avoir'}

      >

      } [Total HT])

       

      Could Some one help please,

       

      Thank you verry much.

        • Re: Complex Conditianal Sum with Max ,Agg  row fields
          Sunny Talwar

          May be this

           

          Date(Max(Date#(Year_ca * 100 + Month_ca, 'YYYYMM')), 'YYYY - MM') & Chr(10) &

          FirstSortedValue(Aggr(Sum(Value), Year_ca, Month_ca), -Aggr(Date#(Year_ca * 100 + Month_ca, 'YYYYMM'), Year_ca, Month_ca))

          • Re: Complex Conditianal Sum with Max ,Agg  row fields
            Belguith Chachia

            Thank you Sunny, i'll test your answer right now.
            by the way i already found a solution after some hourse of documentation reading .

             

            i have a variable  MonthlyRecord , i substract a value "ReturnedCash" wich i Sum from another table with these conditions
            Month and Year corresponding to Month and Year of the MonthlyRecord.
            So the first expression gets the converted month to a Date and the second gets the Year , put the hole thing in a sum and it's done !

             

            Hopes it easy to read !!!

             

            this is how i did for the conditions :


            <  

            [Date.autoCalendar.Month] ={'$(=Month(Makedate((Num#(FirstSortedValue( aggr(Year_CA,Month_CA,Year_CA), aggr(-Max(CA_Month),Year_CA,Month_CA) ),'0')), (Num#(FirstSortedValue( aggr(Month_CA,Year_CA,Month_CA), aggr(-Max(CA_Month),Month_CA,Month_CA) ),'0')) )))'},

                   

            [Date.autoCalendar.Year] = {'$(=Year(Makedate((Num#(FirstSortedValue( aggr(Year_CA,Month_CA,Year_CA), aggr(-Max(CA_Month),Year_CA,Month_CA) ),'0')), (Num#(FirstSortedValue( aggr(Month_CA,Year_CA,Month_CA), aggr(-Max(CA_Month),Month_CA,Month_CA) ),'0')) )))'},

             

            [ODS_GLOBAL.Type de pièce] ={'Avoir'}

             

            >

             

            Best regards