Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rkspareek1992
Partner - Creator
Partner - Creator

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
sunny_talwar

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

View solution in original post

4 Replies
luismadriz
Specialist
Specialist

I hope this helps,

Cheers,

Luis

Untitled.png

sunny_talwar

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
Partner - Creator
Partner - Creator
Author

Thanks for your help..

kaylaramsey2
Partner - Contributor III
Partner - Contributor III

Hi!  This got me exactly what I needed!  Thank you for posting.  However one question...

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

I'm using the above formula but I would only like to see the past 12 months (so the past 4 quarters).  How would I adjust to show that?