Hopefully this will be a simple one for you to solve. Basically, I have a single table that contains 2 date fields (Date Created and Date Closed). I want to do some analysis where I show No. of records created in the month against the number of records closed within the same month. Until now I have had 2 calendars setup, one for Creation Dates and the other for Closure dates, but now I really need a single shared calendar that links to both fields somehow.
Can I structure my code to allow this without any loops or synthetic keys? Can I do this on the object itself? Or do I need to use set analysis for this one?
The suggested approach works, however it requires checking the Dates in all expressions, which is a bit too much trouble and perhaps not too good for the in memory performance.
I'd recommend normalizing the table (which will double the number of rows) and having 2 events for each ticket. The table structure would look like:
Ticket
Description
Event (Open/Close)
Date
Open_Flag (1 or null())
Close_Flag (-1 or null())
This way, you can always claculate whatever you need without any IF statements in your expressions:
I am sure there are many (even better) solutions but one idea could be that you load all distinct dates to an independent Timeline table and then put this Timeline table as time dimension on the sheet. Then in the charts/tables, you can compare the dates field with the date field in the time dimension field. Example:
Timeline: LOAD Distinct STARTDATE as Date resident Tickets;
CONCATENATE (Timeline) LOAD Distinct ENDDATE as Date resident Tickets;
Left join (Timeline) LOAD *, month(Date) as Month, year(Date) as Year; LOAD distinct Date resident Timeline;
Now in chart you can put Month (field of TimeLine) in the dimention and expressions could be - count(distinct if (STARTDATE = Date, Ticket)) - count(distinct if (ENDDATE= Date, Ticket))
I am pretty sure you could also break this table into two and somehow put together a link table maybe. Let's wait for an experts to pick this one. 🙂
The suggested approach works, however it requires checking the Dates in all expressions, which is a bit too much trouble and perhaps not too good for the in memory performance.
I'd recommend normalizing the table (which will double the number of rows) and having 2 events for each ticket. The table structure would look like:
Ticket
Description
Event (Open/Close)
Date
Open_Flag (1 or null())
Close_Flag (-1 or null())
This way, you can always claculate whatever you need without any IF statements in your expressions:
Excellent Oleg. I knew there would be a better way to do it.
I have another similar problem where I got about 20 dates (or more) fields connecting to the Ticket. I implemented what I suggested but I always struggle wondering how to make it more optimized.
But no doubt, for the given example, your solution works like a charm. Thanks.
