Forum Discussion
Work out dates between one column on multi rows
In the attached, the same query adapted to a changed column name from Date Notified to createdon.
It produces just short of 900k rows so I've reduced it to one row (to be able to attach it here) which means you'll need to refresh the query to see those rows. it takes a few seconds to generate those rows…
In the attached workbook (stolen from SundarRaj !) I've put a variation of his query; it's based on grouping by ceox_incidentid (the only one I can reasonably group on) but I notice in the .pbix file that you might instead want to group on ceox_auditlogid, for which I'll have to tweak.
Your limited source table is on Sheet2 and the query result is on Sheet1.
However there's another question: in the attached, the column DatesLists has inclusive dates, meaning that the cross-over dates appear with both old value and new value:
Is this how you want it?
You should add to the data table on Sheet2 then refresh the query on Sheet1 (right-click somewhere in that table and choose Refresh) to check that it's giving you the results you want.
(There's another thing I haven't so far coded for and that's for the possibility of any incident going 'backwards', that is for example, could it go from Awaiting Validation to Pending Information, and later go back to Awaiting Validation?)
Hi, Thank you for getting back to me.
For the first questions i would like the record for 'ceox_newvalue pending information' not to be there for the 10/03.
Second question no it shouldnt.
Ive added the query to myreport but I am getting the error on some records when expanding:
Thanks for all your help
re:"For the first questions i would like the record for 'ceox_newvalue pending information' not to be there for the 10/03."
I've updated the file I attached to previous message, perhaps you can confirm it gives the right dates now.
re: "but I am getting the error on some records when expanding"
I'd really need to see the source data that causes that; I've had a go at guessing what might cause it but without success…
You could replace the existing source data in my file with your error-causing data then save and attach it here.
In the attached, the same query adapted to a changed column name from Date Notified to createdon.
It produces just short of 900k rows so I've reduced it to one row (to be able to attach it here) which means you'll need to refresh the query to see those rows. it takes a few seconds to generate those rows…