Current Fiscal Year and Carry Over Fiscal Year

[rammarthi]

I have a table as below:

Work Order No	Work Order Date	Payment Received Date	Fiscal Year	Payment Value
A001	10/03/2015	10/03/2015	15	5000
A001	10/03/2015	15/04/2015	16	3000
A002	10/07/2016	10/07/2015	16	3000
A002	10/07/2016	10/06/2016	17	7000
A002	10/07/2016	11/07/2016	17	5000
A003	-	10/05/2016	17	6000
A003	-	11/05/2017	18	1000

Now that I wanted Current Fiscal Year and Carry Over Fiscal Year for each Work Order Received. Wherein Current fiscal year is the fiscal year against the work order date. If work order date is not there then lease payment received date should be taken as a work order date and the fiscal year against that row should be current fiscal year.

Now when I Select a work order (or a list of work orders) I should get current fiscal year, carry over fiscal year and payment received in current year and payment received in carry over fiscal year.

Output:

Work Order No	Current Fiscal Year	Carry Over Fiscal Yr	Current Yr Payment Received Value	Carry Over Yr Payment Recieved
A001	15	16	5000	3000
A002	16	17	3000	12000
A003	17	18	6000	1000

[sunny_talwar · Accepted solution]

Try this

Dimension:

[Work Order No]

Expressions:

1) =Max([Fiscal Year], 2)

2) =Max([Fiscal Year])

3) =FirstSortedValue(Aggr(Sum([Payment Value]), [Fiscal Year], [Work Order No]), -Aggr([Fiscal Year], [Fiscal Year], [Work Order No]), 2)

4) =FirstSortedValue(Aggr(Sum([Payment Value]), [Fiscal Year], [Work Order No]), -Aggr([Fiscal Year], [Fiscal Year], [Work Order No]))

[rammarthi]

This worked out Sunny Thanks a lot.....!!!!

[rammarthi]

Hi Sunny,

Is there any way I can define this output table in backend rather than in front end please let me know.

Regards

Ram Marthi

[sunny_talwar]

May be this:

Table:

LOAD [Work Order No],

    [Work Order Date],

    [Payment Received Date],

    [Fiscal Year],

    [Payment Value]

FROM

[https://community.qlik.com/thread/229071]

(html, codepage is 1252, embedded labels, table is @1);

AggregatedTable:

LOAD [Work Order No],

  [Fiscal Year],

  Sum([Payment Value]) as Payment

Resident Table

Group By [Work Order No], [Fiscal Year];

DesiredResult:

LOAD [Work Order No],

  Max([Fiscal Year], 2) as [Current Fiscal Year],

  FirstSortedValue(Payment, -[Fiscal Year], 2) as [Current Year Payment Received Value]

Resident AggregatedTable

Group By [Work Order No];

Left Join (DesiredResult)

LOAD [Work Order No],

  Max([Fiscal Year]) as [Carry Over Fiscal Year],

  FirstSortedValue(Payment, -[Fiscal Year]) as [Carry Over Year Payment Received Value]

Resident AggregatedTable

Group By [Work Order No];

DROP Table AggregatedTable;