Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor II

How to calculate the first day of a different status

Hi! Please let me know is it possible to make to a script to find first day of StatusA and the fist day of StatusB and calculates the days between those.

Data is sort by CustomerID, TransactionData, Status

 

CustometIDTransactionDateStatusStatusAStatusBQliView should calculate CountDays 
80031326907.9.2015StatusA7.9.2015   
800313269031.12.2015StatusA31.12.2015   
800313269029.2.2016StatusA29.2.2016   
80031326901.6.2016StatusA1.6.2016   
800313269019.9.2016StatusB 19.9.2016378= 19.9.2016 - 7.9.2015
800313269027.3.2017StatusB 27.3.2017  
80031326901.6.2017StatusA1.6.2017   
80031326902.10.2017StatusA2.10.2017   
80031326903.1.2018StatusA3.1.2018   
800313269017.5.2018StatusA17.5.2018   
80031326909.8.2018StatusA9.8.2018   
800313269022.8.2018StatusB 22.8.2018447=22.8.2018 - 1.6.2017
800313269025.9.2018StatusA25.9.2018   
800313269018.3.2019StatusB 18.3.2019174=18.3.2019 - 25.9.2018
80031326903.6.2019StatusA3.6.2019   
80031326905.6.2019StatusB 5.6.20192=5.6.2019 - 3.6.2019
80031326906.6.2019StatusB 6.6.2019  
80031326906.8.2019StatusA6.8.2019   
800313269019.8.2019StatusB 19.8.201913=19.8.2019 - 6.8.2019
     1014Total_Days
2 Replies
Highlighted
Digital Support
Digital Support

Re: How to calculate the first day of a different status

Arto, I am no expert here, but hopefully my post will get this back to the top of things and someone else with a bit more experience can provide some additional ideas on things.  I believe you are going to need to use Order or Group by to get things sorted on the load, then you may need to use Peek to get the first value of each etc.

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Inter...

After that, just a matter of using the date functions to get the numeric value of each date and subtract those to get the delta I believe, but someone else may have something better here.  

Here is another Community post with a solution on the difference calc:

https://community.qlik.com/t5/QlikView-Creating-Analytics/Number-of-days-between-dates/m-p/328986#M1...

I am pretty sure there is likely one on getting your first date from each status too, but that one was a bit more difficult to figure out what to search upon as far as key words.  Sorry I do not have anything better, but hopefully this gives you a path forward.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Highlighted
Digital Support
Digital Support

Re: How to calculate the first day of a different status

See duplicate post for further information:

https://community.qlik.com/t5/QlikView-Scripting/How-to-calculate-the-first-day-of-a-different-statu...

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.