Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
LastValue(Money) group by Day
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.
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;
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.
Can you please give us a sample of your QVW, so that we can take a look and develop a solution?
unfortunately I can not
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)