Scripting help - Max expression

[#1]

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

[Anonymous]

LastValue(Money) group by Day

[jonathandienst]

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;

[#4]

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.

[miguelbraga]

Can you please give us a sample of your QVW, so that we can take a look and develop a solution?

[#6]

unfortunately I can not

[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)