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

    How to get specific year per owner in a given range

    mark wall

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