topic Re: Scripting help - Max expression in QlikView

Scripting help - Max expression

Wed, 14 Oct 2015 08:14:44 GMT

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

Anonymous — Wed, 14 Oct 2015 08:17:22 GMT

LastValue(Money) group by Day

Re: Scripting help - Max expression

jonathandienst — Wed, 14 Oct 2015 08:32:26 GMT

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

Wed, 14 Oct 2015 08:42:17 GMT

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

miguelbraga — Wed, 14 Oct 2015 08:53:57 GMT

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

Re: Scripting help - Max expression

Wed, 14 Oct 2015 08:56:38 GMT

unfortunately I can not

Re: Scripting help - Max expression

Kushal_Chawda — Wed, 14 Oct 2015 09:08:31 GMT

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)