# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Contributor

## Amount at Maximum date of quarter

Hi,

I have data at every date for the current fiscal year. And I wants the data as on maximum date of respective quarter.

I have attached the raw data format and desired output.

Kindly suggest expression.

1 Solution

Accepted Solutions
MVP

## Re: Amount at Maximum date of quarter

Two options

1) Create flag in the script like this

Table:

[Fiscal Quarter],

Amount,

[Fiscal Quarter] & Year(YearStart(Date, 0, 4)) as [Fiscal Quarter Year],

Year(YearStart(Date, 0, 4)) as [Fiscal Year]

FROM

(ooxml, embedded labels, table is Data);

Left Join (Table)

Max(Date) as Date,

1 as MaxQuarterDateFlag

Resident Table

Group By [Fiscal Quarter Year];

and then use this expression

=Sum({<MaxQuarterDateFlag = {1}>}Amount)

2) Use this expression without adding flag in the script

=Sum(Aggr(If(Date = Max(TOTAL <[Fiscal Quarter Year]> Date), Sum(Amount)), [Fiscal Quarter Year], Date))

or if you only have single year data, then this

=Sum(Aggr(If(Date = Max(TOTAL <[Fiscal Quarter]> Date), Sum(Amount)), [Fiscal Quarter], Date))

3 Replies
Valued Contributor

## Re: Amount at Maximum date of quarter

I hope this helps,

Cheers,

Luis

MVP

## Re: Amount at Maximum date of quarter

Two options

1) Create flag in the script like this

Table:

[Fiscal Quarter],

Amount,

[Fiscal Quarter] & Year(YearStart(Date, 0, 4)) as [Fiscal Quarter Year],

Year(YearStart(Date, 0, 4)) as [Fiscal Year]

FROM

(ooxml, embedded labels, table is Data);

Left Join (Table)

Max(Date) as Date,

1 as MaxQuarterDateFlag

Resident Table

Group By [Fiscal Quarter Year];

and then use this expression

=Sum({<MaxQuarterDateFlag = {1}>}Amount)

2) Use this expression without adding flag in the script

=Sum(Aggr(If(Date = Max(TOTAL <[Fiscal Quarter Year]> Date), Sum(Amount)), [Fiscal Quarter Year], Date))

or if you only have single year data, then this

=Sum(Aggr(If(Date = Max(TOTAL <[Fiscal Quarter]> Date), Sum(Amount)), [Fiscal Quarter], Date))

Contributor