Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
I hope this helps,
Cheers,
Luis
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))
Thanks for your help..
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?