Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
rammarthi
Creator
Creator

Current Fiscal Year and Carry Over Fiscal Year

I have a table as below:

Work Order NoWork Order DatePayment Received DateFiscal YearPayment Value
A00110/03/201510/03/2015155000
A00110/03/201515/04/2015163000
A00210/07/201610/07/2015163000
A00210/07/201610/06/2016177000
A00210/07/201611/07/2016175000
A003-10/05/2016176000
A003-11/05/2017181000

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 NoCurrent Fiscal YearCarry Over Fiscal YrCurrent Yr Payment Received ValueCarry Over Yr Payment Recieved
A001151650003000
A0021617300012000
A003171860001000
1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

View solution in original post

4 Replies
sunny_talwar

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

Capture.PNG

rammarthi
Creator
Creator
Author

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

rammarthi
Creator
Creator
Author

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;