Hi @1460051556, If what you want to accomplish is a olap table with specific states for a specific date time, I would suggest you work with time picker to select the day and with activity selection (selecting only the states you want to see), I think it would work (if I understood the questions)
Hope it helps
Hi @1460051556, If what you want to accomplish is a olap table with specific states for a specific date time, I would suggest you work with time picker to select the day and with activity selection (selecting only the states you want to see), I think it would work (if I understood the questions)
Hope it helps
@javier.donos thanks for answer!
Yes, you right. This way is working well, but not exactly what i need.
We got case with some steps in it:
- Created - Jan 01 2020
- Signing A - Jan 01 2020
- Signing B - Jan 02 2020
- Signing C - Jan 03 2020
- Complete - Jan 08 2020
We got 2 elements on list:
- OLAP Table with data
- Column Chart with spread cases by EventTime
All data filtered by PROCESS EQUALS 'Signing C' END.
I need to count DAYS_BETWEEN("EVENT_TIME", SELECTED DATE())
Where SELECTED_DATE() - abstract function, that returns selected date on the Column Chart (second element)
So, described above case can't be handled by script, because his actual (TODAY()) process step is NOT 'Signing C'.
But, i need to count it, if on Column Chart i filter by Jan 07 2020, and the result gona be '4'.
I tried workaround with
DAYS_BETWEEN("EVENT_TIME", MAX("EVENT_TIME")) (we got enough data, so we always got at least one event for this trick)
But, obviously it's failed, because MAX() - returns not a DATE, and i did not found solution to convert it to timestamp for comparison with "EVENT_TIME".
TO_TIMESTAMP(MAX("EVENT_TIME"),'YYYY-MM-DD') makes no sense for DAYS_BETWEEN, i still cant compare this two values. May be you know some tips for this convertation?
Best regards <3
Hi again @1460051556 , thanks for the clarifications, if I summarize:
1) you filter the component/sheet by process end == 'signing C', doing that you restrict the cases that follow this pattern regardless of the time filter of the analysis
2) In the column chart are displayed per day the different states('Signing A', 'Signing B', ' Signing C'), per day.
In this case as the component filter is process end == 'signing C',
If we select in the column chart today's date, normally the current state for all activities are 'Signing C'
If we select in the column chart one previous week day we will get the cases that were already in 'Signing C' and other cases in other states 'Signing A', 'Signing B', that they change to 'Signing C' in the last week.
Are these the cases that you want to report?
Or are you looking for each date how many cases are at stage ' 'Signing C' and how many days where in 'Signing C'?
If this later is the case I would work with a script base filter as you are proposing but linked with a date variable:
One solution would be using cases as dimension (not activities) and using pull functions, as you are filtering by the process pattern it means that the last activity is 'Signing C', you can then use the PU_LAST() <= <%=variable date%> in the script and should work
Hope it helps, I wrote it down to understand better the problem while writing.
Javier
