Create Start and End Date from one date field

[akpofureenughwu]

Hello everyone, 

My company recently started a campaign program last year. We need to monitor our performance. We have a list of records in this format.

Date	Telephone Number	Campaign
01/01/2020	1234551	Promotion 10
01/01/2020	1234552	Promotion 11
01/01/2020	1234567	Promotion 1
01/01/2020	1234566	Promotion 2
06/01/2020	1234551	Promotion 10
06/01/2020	1234552	Promotion 11
06/01/2020	1234567	Promotion 4
06/01/2020	1234566	Promotion 7

from the above table,  we need to express the records in the format below

S/N	Telephone	Campaign	Start Date	End Date	Duration (days)(
1	1234551	Promotion 10	1/1/2020	still alive	On-going
2	1234552	Promotion 11	1/1/2020	still alive	On-going
3	1234567	Promotion 1	1/1/2020	1/5/2020	5
4	1234566	Promotion 2	1/1/2020	1/5/2020	5
5	1234567	Promotion 4	1/6/2020	still alive	On-going
6	1234566	Promotion 7	1/6/2020	still alive	On-going

Please can anyone help me with a clue? 

Regards

[Brett_Bleess · Accepted solution]

Have a look at the following Design Blog post, it may put you on the right track, but I am not sure:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

If you wish to do some further searching in this area, use the following link:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

There are hundreds of mostly how-to related posts in this area from our experts, so it may prove very useful to you in coming up with ideas.

Regards,
Brett

[akpofureenughwu]

Hello BrettBleess,

Thank you for the link.. 

https://community.qlik.com/t5/Qlik-Design-Blog/Creating-a-Date-Interval-from-a-Single-Date/ba-p/1467...

The link above is helpful however rather than the todate for a running campaign to be today's date can it be change to on-going?

Regards

[Brett_Bleess]

Best I have is the following, not sure it is what you need:

https://community.qlik.com/t5/Qlik-Design-Blog/Slowly-Changing-Dimensions/ba-p/1464187

https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

https://community.qlik.com/t5/Qlik-Design-Blog/Accumulations/ba-p/1466581

Hopefully one of those may help further.

Regards,
Brett