Skip to main content
Question

How can I have a common table on the temporary table from GENERATE_RANGE function and the case table

  • September 27, 2024
  • 2 replies
  • 93 views
xiangxiang.chen11
Level 2
Forum|alt.badge.img

Hello,

 

I need a line chart showing created/open/resolved cases count every day.

​

For x axis, I used GENERATE_RANGE function to generate DATE values as below.

GENERATE_RANGE ( '1D' , TO_DATE ('2024-01-01', FORMAT('%Y-%m-%d')), TODAY())

 

For y axis(KPI), I use:

COUNT_TABLE ( "casetable" )

 

However, the problem is there's no common table between this generated temporary table with the case table or activity table, so the case counts cannot be calculated based on this temporary table.

 

 

image​

Does anyone know a good way to create that analysis, ideally only in frontend and not change the data model?

carlos.taleg11

2 replies

florian.mrkvi
Level 7
Forum|alt.badge.img

Hi!
I am not sure why you are trying to useg the GENERATE_RANGE function for the x-axis. Normally you would use a date field from your case table which is already there or which is being added by a PULL_UP function.

If you just generate a date range (x-axis) Celonis doesn’t know which day the case belongs to. At least in your description I can’t see any date that belongs to the case. Where is the date info stored for each case?

dxb.miner
Level 5
  • dxb.miner
  • Level 5
  • October 23, 2024

@xiangxiang.chen11 

I would suggest, you use a date column like Last modified date at case level in the dimension and in KPIs create separate count KPIs for each of the case types like created/open/resolved using status or any other filter in the count.

 

 

Community Code of ConductAccessibility statement