Dynamically create a field on the basis of two dates

ruma_barman — Tue, 07 Sep 2021 12:52:09 GMT

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 ,

	Count
InStock	1	(A1)
Sold	0

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

topic Dynamically create a field on the basis of two dates in App Development

Dynamically create a field on the basis of two dates