6 Replies Latest reply: Oct 14, 2015 5:08 AM by kushal chawda RSS

    Scripting help - Max expression

      Hi,

      In have the following wrong expression which brings me the MAX value from aggregated money formula in a flexible selected period:

      =Max (Aggr (Sum (Money),[Day\Month\Year],moneytype))  I should somehow plant the MAX before the day\month\year field BUT I do not know how to script it properly. 

      How do I script this expression so that it will bring me the LATEST (money) value from the flexible selected period.

      --  Like it does successfully in a simple SUM expression which I tested in a test data : Sum({<Date={'$(=Max(Date))'}>} money

      By the way I tried to write it as LastSortedValule (Aggr (Sum (Money),[Day\Month\Year],moneytype))   or LastSortedValue( Aggr (Sum (Money),[Day/Month/Year],MoneyType), - [Day/Month/Year]) but it does not work.

      The day\month\year is set properly is a date field.       

       

      Nir

        • Re: Scripting help - Max expression
          balraj ahlawat

          LastValue(Money) group by Day

            • Re: Scripting help - Max expression
              Jonathan Dienst

              In the front end, the expression is evaluated in the context of the chart/table dimensions. Without knowing this information I can only give general guidelines of how to do it in script. You will need to fill in the details.

               

              • After loading the fact table, do a resident load, with the Max(date), grouping by the appropriate dimensions.
              • Join this table to the fact table
              • The max date values are the ones where Max(date) = date. Set a flag on these values.

               

              Something like this (assumes the fact table is called Fact - change to suit):

               

              Join (Fact)

              LOAD Max([Day/Month/Year]) As MaxDMY,

                dimension1,                // the dimensions matching the front end chart

                dimension2

              Resident Fact

              Group By

                dimension1,

                dimension2;

               

              Join (Fact)

              LOAD ID,                    // one or more fields that uniquely identify the Fact table rows

                [Day/Month/Year],

                MaxDMY,

                If([Day/Month/Year] = MaxDMY, 1, 0) As FlagMaxDMY

              Resident Fact;

                • Re: Scripting help - Max expression

                  Hi Jonathan,

                  Thank you for your effort but I am looking for (am sure that there is ) a more simple solution.

                  My chart dimensions are : day\month\year and the moneyType.

                  The user can select from list the relevant period of time (April 2014, last week or the year 2013 and set)

                  Is I wrote I have managed to solve this issue for a simple sum expression: Sum({<Date={'$(=Max(Date))'}>} money but this expression was not group by moneytype and was not using aggr (which I must since I collect data from different sources (country, city and so on)

                  I only need to script , if possible, the MAX expression before the date so that the expression will pick the lastest value like it did in the simple sum expression.

                   

              • Re: Scripting help - Max expression
                kushal chawda

                Data:

                load  date(Date,'DD/MM/YYYY') as Date

                Key,

                Money,

                moneytype,

                Dimension2

                From Table;

                 

                Left join(Data)

                load date(max(Date),'DD/MM/YYYY') as Date,

                Key,

                moneytype,

                1 as MaxDateFlag

                Resident Data

                group by Key,moneytype;



                Now you can write the expression


                =sum({<MaxDateFlag={'1'}>}Money)