Hi All,
Please help me in getting the logic in QlikSense . FrontEnd or Backend either will do.
I have to create a field viz. RepoStatus having two values 'InStock' and 'Sold' .These two values should be on the basis of dates.
Input :
| Contract | Repo Date | Sold Date |
| A1 | 21-01-2021 | 28-03-2021 |
| A2 | 01-02-2021 | 05-08-2021 |
| A3 | 12-04-2021 | 28-04-2021 |
| A4 | 23-04-2021 | |
| A5 | 06-06-2021 | 28-05-2021 |
| A6 | 17-07-2021 | 28-06-2021 |
| A7 | 28-08-2021 | 28-08-2021 |
| A8 | 05-09-2021 | |
Now I have to count the number of YTD contracts on the RepoStatus logic as below:
If a month is selected and the contract repodate is less or equal to the month and
1)Sold Date is blank or greater than the month selected it will be considered as InStock
2)Sold Date is less or equal to the month selected it will be considered as Sold
Output:
If Jan2021 is selected ,
If Feb2021 is selected ,
| | Count | |
| InStock | 2 | (A1,A2) |
| Sold | 0 | |
If Mar2021 is selected ,
| | Count | |
| InStock | 1 | (A2) |
| Sold | 1 | (A1) |
If Apr2021 is selected ,
| | Count | |
| InStock | 2 | (A2,A4) |
| Sold | 2 | (A1,A3) |
P.S:The contracts in brackets are given for reference,not required in the table
Thanks and Regards,
Ruma
