Consider data based on availability

[P_Kumar]

Hi,

I have a requirement to show data only till available month.

For eg: Bonus is the file which can be given once in year and may be multiple months in a year.  Let us assume, we got the bonus file three times this year Apr'18, Jul'18, Oct'18.

After loading Bonus data,

1.if a user select Mar'18 from filter it should show '0' (since we have no bonus announced till march)

2. If a user select June'18 from filter it should show 'Apr18' bonus data (since we have data for Apr'18)

3. If a user select Nov'18 from filter it should show  'Oct18' bonus data (since the all bonus files are cumulative in nature.. i.e. Oct18 file consists Apr18 and Jul18 bonus data)

Kindly advise.

[jonathandienst]

Assuming the bonus is linked to and employee then

Sum(Aggr(FirstSortedValue(Bonus, -BonusFileDate), EmployeeCode))

If the data spans multiple years, then you will need a year field derived from he bonus file date, and then use:

Sum(Aggr(FirstSortedValue(Bonus, -BonusFileDate), EmployeeCode, BonusFileYear))

[P_Kumar]

HI 

thank you for quick reply,  we don't have date filed in the data , we have year and month fields .

I have used you suggested expression but it is not working.

like below 

Sum(Aggr(FirstSortedValue(ONE_OFF_ACTUAL_EXPIRY, -Month), SAP_CODE))

can you give any suggestion.

Thank you very much for giving suggestion.