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 | > $1M | Grand 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 $50k | Between $50k and less than $100k | Between $100k and less than $250k | Between $250k and less than $500k | Between $500k and less than $1M | Equal 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
