Set Analysis for max function

[#1]

I have a table that contains records that I would like to pull based on user input

The two fields in particular that I would like to use to get my value is fiscal_year and yr_prd

I have a variable that the user will enter a value for called vFiscalYr.

I need to have the set analysis pull the record that contains the fiscal year the user entered but it must pull the max for yr_prd.

The field that I want to display is called beg_balance.

how can I set this up in set analysis to pull the beg_balance based on the max yr_prd for a specific year????

[sunny_talwar]

Getting a little confused here, what is yr_prd here and how it is related to fiscal_year?? Can you provide some sample data with expected output?

Best,

S

[maxgro · Accepted solution]

sum({$ <fiscal_year={$(vFiscalYr)},yr_prd={$(=max(yr_prd))}>} beg_balance )

[#4]

sunindia

here is a sample of the table.

so if the user enters 2014

then it should pull $494.76 since this is the max yr_prd for 2014.

I'm trying to pull the Current_Yr_Depreciation.(sorry, I think I said begBalance earlier but it's Current_Yr_depreciation)

Item	Fiscal_Year	BegBalance	BegDepreciation	YR_PRD	Current_Yr_Depreciation
16239	2014	$0.00	$3,591.00	201401	$41.23
16239	2014	$0.00	$3,591.00	201402	$82.46
16239	2014	$0.00	$3,591.00	201403	$123.69
16239	2014	$0.00	$3,591.00	201404	$164.92
16239	2014	$0.00	$3,591.00	201406	$247.38
16239	2014	$0.00	$3,591.00	201407	$288.61
16239	2014	$0.00	$3,591.00	201408	$329.84
16239	2014	$0.00	$3,591.00	201409	$371.07
16239	2014	$0.00	$3,591.00	201410	$412.30
16239	2014	$0.00	$3,591.00	201411	$453.53
16239	2014	$0.00	$3,591.00	201412	$494.76
16239	2015	$0.00	$3,591.00	201501	$0.00
16239	2015	$0.00	$3,591.00	201502	$0.00

[maxgro]

sum({$ <Fiscal_Year={$(vFiscalYr)},YR_PRD={"$(=max({1 <Fiscal_Year={$(vFiscalYr)}>} YR_PRD))"}>} Current_Yr_Depreciation )