How to get specific year per owner in a given range

Sun, 06 Mar 2016 02:37:02 GMT

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

swuehl — Sun, 06 Mar 2016 12:19:50 GMT

I can't see your attachments.

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]

topic Re: How to get specific year per owner in a given range in App Development

How to get specific year per owner in a given range

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