Dear all,
I need to offset cash applied USD per day in May 2018 against the total invoiced amount USD in May 2018, in one table.
Expression used for invoice amount USD in May 2018 which has a result off 70.000 USD.
| =sum({<[Report Year Month]={'201805'},[Type]={'INV'}>}[Invoice AMT USD] |
Expression used for cash applied for May 2018 which has a result off 55.000 USD.
| '=sum({<[Report year month]={'201805'},[Application type]={'CASH'}>}[Cash applied AMT USD] |
=day([Report Date]) = used for May 2018 day number
The table which I need to create is to see what the % of cash applied per day for May 2018 is against the total invoice in May 2018.
The final result in QlikSense should be:
| Day number | % of cash receipt per day |
| 1 | 2.9% |
| 2 | 1.4% |
| 3 | 0.7% |
| 4 | 4.3% |
| 5 | 3.6% |
| 5 | 5.7% |
| 6 | 1.1% |
| 7 | 0.3% |
| 8 | 1.4% |
| 9 | 2.9% |
| 10 | 1.7% |
| 11 | 1.0% |
| 12 | 1.1% |
| 13 | 0.4% |
| 14 | 5.7% |
| 15 | 4.3% |
| 16 | 1.4% |
| 17 | 2.9% |
| 18 | 3.6% |
| 19 | 0.7% |
| 20 | 4.3% |
| 21 | 2.9% |
| 22 | 1.4% |
| 23 | 2.1% |
| 24 | 4.3% |
| 25 | 3.3% |
| 26 | 1.0% |
| 27 | 1.1% |
| 28 | 0.3% |
| 29 | 1.9% |
| 30 | 4.6% |
| 31 | 4.3% |
And the calculation is as following where in the dimension I need to fill in =day([Report Date]), what do I need to use as a measurement in order to get the right %?
| Day number | Cash applied | | Invoiced amount USD | | % of cash receipt per day |
| 1 | $2,000.00 | / | $70,000 | = | 2.9% |
| 2 | $1,000.00 | / | $70,000 | = | 1.4% |
| 3 | $500.00 | / | $70,000 | = | 0.7% |
| 4 | $3,000.00 | / | $70,000 | = | 4.3% |
| 5 | $2,500.00 | / | $70,000 | = | 3.6% |
| 5 | $4,000.00 | / | $70,000 | = | 5.7% |
| 6 | $800.00 | / | $70,000 | = | 1.1% |
| 7 | $200.00 | / | $70,000 | = | 0.3% |
| 8 | $1,000.00 | / | $70,000 | = | 1.4% |
| 9 | $2,000.00 | / | $70,000 | = | 2.9% |
| 10 | $1,200.00 | / | $70,000 | = | 1.7% |
| 11 | $700.00 | / | $70,000 | = | 1.0% |
| 12 | $800.00 | / | $70,000 | = | 1.1% |
| 13 | $300.00 | / | $70,000 | = | 0.4% |
| 14 | $4,000.00 | / | $70,000 | = | 5.7% |
| 15 | $3,000.00 | / | $70,000 | = | 4.3% |
| 16 | $1,000.00 | / | $70,000 | = | 1.4% |
| 17 | $2,000.00 | / | $70,000 | = | 2.9% |
| 18 | $2,500.00 | / | $70,000 | = | 3.6% |
| 19 | $500.00 | / | $70,000 | = | 0.7% |
| 20 | $3,000.00 | / | $70,000 | = | 4.3% |
| 21 | $2,000.00 | / | $70,000 | = | 2.9% |
| 22 | $1,000.00 | / | $70,000 | = | 1.4% |
| 23 | $1,500.00 | / | $70,000 | = | 2.1% |
| 24 | $3,000.00 | / | $70,000 | = | 4.3% |
| 25 | $2,300.00 | / | $70,000 | = | 3.3% |
| 26 | $700.00 | / | $70,000 | = | 1.0% |
| 27 | $800.00 | / | $70,000 | = | 1.1% |
| 28 | $200.00 | / | $70,000 | = | 0.3% |
| 29 | $1,300.00 | / | $70,000 | = | 1.9% |
| 30 | $3,200.00 | / | $70,000 | = | 4.6% |
| 31 | $3,000.00 | / | $70,000 | = | 4.3% |
Thanks
