0 Replies Latest reply: Jul 24, 2017 2:44 AM by Marcelo Mtanous RSS

    Evaluate field for Current and Prior Year

    Marcelo Mtanous

      Hi,

       

      I need help to solve this. I am analyzing client trends and I want to be able to identify clients who are jumping or falling from the different client tiers or bands. Below is the extract from an excel table.

       

       

          

      Amount

      Tier Last Year

      Tier Current Year

      New Client< $50k> $50k < $100k> $100k < $250k> $250k < $500k> $500k < $1M> $1MGrand Total
      < $50k$245,943 $1,334,778 $61,546 $4,758 $3,824 $0 $0 $1,650,849
      > $50k < $100k$148,460 $211,127 $1,250,198 $92,552 $4,313 $4,105 $0 $1,710,754
      > $100k < $250k$267,609 $102,873 $418,244 $3,252,261 $176,388 $20,875 $0 $4,238,250
      > $250k < $500k$106,885 $36,186 $44,777 $574,819 $3,534,059 $234,004 $24,491 $4,555,220
      > $500k < $1M$89,750 $25,000 $0 $42,249 $855,988 $4,786,004 $154,591 $5,953,582
      > $1M$0 $0 $0 $0 $58,850 $525,568 $41,661,270 $42,245,688
      Grand Total$858,648 $1,709,963 $1,774,765 $3,966,638 $4,633,422 $5,570,556 $41,840,351 $60,354,344

       

      Here is the table I am able to generate in Qlik. I am able to match those clients who remain in the same tier YoY but all the movers are kept unassigned in the last column. I am using an aggr dimension for last year client size.

       

      How can I identify the clients tier from last year but display it with the amount of current year?  The tricky part is that it is one expression, for current year, but two client size dimensions, one evaluated with current amount and one with last year amount.

      At the end, the only clients who should remain unassigned would be labeled as 'New Clients'

       

       

      ClientSize Client Size LastYear Equal or less than $50kBetween $50k and less than $100kBetween $100k and less than $250kBetween $250k and less than $500kBetween $500k and less than $1MEqual or greater than $1M-
      Equal or less than $50k $27386-----$5798
      Between $50k and less than $100k -$24619----$10400
      Between $100k and less than $250k --$62847---$20452
      Between $250k and less than $500k ---$72087--$22648
      Between $500k and less than $1M ----$97613-$27452
      Equal or greater than $1M -----$872825$16231
      Total $27386 $24619 $62847 $72087 $97613 $872825 $102981

       

       

      Many thanks for any guidance on this.

       

      Thanks,

      Marcelo