2 Replies Latest reply: Dec 12, 2017 1:02 PM by Ben Roberts RSS

    FirstSortedValue with dates and aggr

    Ben Roberts

      Dear all, I have been battling with this problem for many days now and would like to ask for some help on how to solve.

       

      I have a table of data which contains transactions and mappings to different product types and I am trying to create a table that for each client entry and for each product type:

      1. What is the first date of investment (where amount >0)

      2. What is the amount in the first date of investment

       

      I started off trying to create a set analysis to show the amount on a particular date using a variable for the min month but it got too complex so went down the first value route.

       

      In the attached file I share the data table - I am only interested in Aspect = R and for aggregation of the values at Prod_Cotegory_Desc

       

      The table I am trying to create would end up looking like this:

       

      ClientProduct CategoryFirst  Month of InvestmentInvestment Amount
      Client 1DJan-2017129
      Client 1EAug-20173,663
      Client 1PEJan-20173,361
      Client 2CL&CJan-201725,353
      Client 2DJan-201797

       

      One of the things I noticed with the data was that Qlik was not handling the date field sequentially so I created the FACT_MONTH_YEARDATE_NQA field on the date value so sorting was more simple.

       

      The furthest I got was creating a formula something like this:

       

      First Investment Date:

      AGGR(
      FirstSortedValue(
           AGGR(MIN({$<AMOUNT = {'>0'}>}FACT_MONTHYEAR_DATE),RELN_NBR,PROD_COTEGORY_DESC), //Mind date per prod     
                AGGR(MIN(FACT_MONTHYEAR_DATE),RELN_NBR,PROD_COTEGORY_DESC) //sort by date and product
           )
      ,RELN_NBR,PROD_COTEGORY_DESC)  //aggregate at product level
      

       

      This appears to produce the correct date value.

       

      Amount for first investment date:

      AGGR(
      FirstSortedValue(
           AGGR(SUM({$<AMOUNT = {'>0'},ASPECT = {'R'}>}AMOUNT),RELN_NBR,PROD_COTEGORY_DESC), //Min amount per prod
                AGGR(MIN(FACT_MONTHYEAR_DATE),RELN_NBR,PROD_COTEGORY_DESC) //sort by date and product
      )
      ,RELN_NBR,PROD_COTEGORY_DESC)  //aggregate at product level
      

       

      the result of the above is that it sums the R factor and does not show the minimum value. This minimum value would be the sum of all the values in the minimum month where aspect = R

       

      Any help much appreciated!