Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

rkspareek1992
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

Re: Amount at Maximum date of quarter

Two options

1) Create flag in the script like this

Table:

LOAD Date,

    [Fiscal Quarter],

    Amount,

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

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

FROM

[..\..\Downloads\DataPerDate.xlsx]

(ooxml, embedded labels, table is Data);


Left Join (Table)

LOAD [Fiscal Quarter Year],

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

Capture.PNG

3 Replies
luismadriz
Valued Contributor

Re: Amount at Maximum date of quarter

I hope this helps,

Cheers,

Luis

Untitled.png

Re: Amount at Maximum date of quarter

Two options

1) Create flag in the script like this

Table:

LOAD Date,

    [Fiscal Quarter],

    Amount,

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

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

FROM

[..\..\Downloads\DataPerDate.xlsx]

(ooxml, embedded labels, table is Data);


Left Join (Table)

LOAD [Fiscal Quarter Year],

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

Capture.PNG

rkspareek1992
Contributor

Re: Amount at Maximum date of quarter

Thanks for your help..

Community Browser