How can i partion clause in analysis

Question

I am working on a requirement that I need to capture count of employees  hiring process workflow. Each workflow step Is based on a date and the requirement is to capture only employees that are in each workflow step within a certain date period. At this time my query below Its capturing events that occurred outside of the date range and I wanted to know  if I can use partition clause so that any event that is outside of the date I am looking at is not captured. Here is what I have in my analysis.

FILTER("Fact -"  USING "Worklow Status Name" = 'Offer'  AND "TTY" NOT IN (1) AND "Workflow Name" IN ('Offer to be Made','Extended'))

ForSly · Answer

Can you explain in a  step by step how you used the advanced tab to create partition?

Vipulbhatia29 · Answer

Hi just trying to understand this basically you have a date column and workflow step is based on date. So basically your requirement is something of the sort

Count over (partitio by some column order by some column)

If you can create a case when logic and then create one measure column on report criteria that calculates count (colA by case when date between (start,end) then valA  else ValB end) )

Will that not serve the purpose.

Alternatively can you try the options on advanced tab on the report. I have used that tab on the report to set partition by grouping logic on Group By component in the past and it works.

Thanks

Vipul

Gianni Ceresa · Answer

You mean the Oracle SQL "partition by" clause used with analytical functions?

Nope, the BI Server speaks LSQL, not Oracle SQL. You aren't writing a physical query which will be evaluated by the database but a piece of query which is sent to the BI server.

If you want to send code to the database you need to use evaluate (or one of the similar) functions or put the logic into a view or something which is loaded and mapped in the RPD.