1 Reply Latest reply: Mar 6, 2016 7:19 AM by Stefan Wühl

# How to get specific year per owner in a given range

I have this scenario, two variable fields are available variable1(FROM) and variable2(TO). This represent year in my data. For example if the user enter 2012 in FROM field and 2016 in TO field this will fetch all data that have payment within the given range. My expression works but my problem is I want to disregard data who are late payers. This will determine in ORDATE. Kindly see the data below.

PIN     OWNER     YEAR     ORDATE          AMOUNT

123     Rose          2012        12/12/2013         \$100.00

124     SAM           2014        01/01/2016         \$200.00

125     Mark           2016        10/16/2016         \$300.00

126     Baisah        2015        10/08.2014        \$350.00

Fetch only data whose ordate is less than or equal to year. Hence all data whose ordate is greater than year is not included.

Please see my attachment and my expression any help is appreciated. Thank You!

If you want to see app please see attachment below and find Total collection then find Total Collection mein straight table.

EXPRESSION:

sum({< ordate= {">=1/1/\$(FROM)<=12/31/\$(TO)"}, year1={">=\$(FROM)<=\$(TO)"}, qtr1={"1","2","3","4"},yr={"<=2050"}>} aggr(DISTINCT PayBasic,ordate,qtr1,year1,Pin3))

• ###### Re: How to get specific year per owner in a given range

In general, set analysis is evaluated once per chart, not per dimension line.

So I think you would need something like

sum({< year1={">=\$(FROM)<=\$(TO)"}, qtr1={"1","2","3","4"},yr={"<=2050"}>}

aggr(DISTINCT if(year(ordate)<=year1,PayBasic),ordate,qtr1,year1,Pin3)

[Not sure if you need the advanced aggregation here]