Hi, not sure if FDI supports any formula to perform this action, but I would have used Dataflows, which has a Split transformation function. It allows you to split values into columns using “_” as a delimiter and then keep only the required columns by renaming them.

@Hasan Sd I don’t want to complicate this further, but one last thing you could try is creating a new parameter by clicking on the Create Parameter option and using the same LSQL again. If that still doesn’t resolve it, I’d suggest posting this in Idea Labs, requesting that Oracle add a date format setting in the filter…

No, not referencing the parameter in the metric — just use CAST([Sales Date] AS DATE) and use this new field instead of Sales Date in all your visuals.

Haven't you tried the second approach in my earlier comment ?

In this case, since you’re directly using filters within individual visualizations, you can try either of the following approaches to resolve the issue: Change the data type from DateTime to Date in your data file (as shown in the snapshot). You can also set the required date format (dd/MM/yyyy) at that stage.…

Glad it worked for you, @Hasan Sd ! What I’d suggest is to keep the parameter datatype as Timestamp (Auto), and then go to the visual and change the date format at the visual level from Auto to Custom (dd/MM/yyyy).

Hi, most of it looks good. The only part I can’t verify is whether the reference is correctly set up as the LSQL expects. What I’d suggest is — since the file name shows as _kpi_dataset, please go to Data > (3 dots) > Inspect, and you should find an option to Copy Object ID. That exact ID is what needs to be copy-pasted…

Hi, to do this please set the data type to Timestamp and Auto, then set the initial value to the following Logical SQL: SELECT MAX(CURRENT_DATE) AS Current_Date FROM XSA('<REPLACE_WITH_YOUR_DATASET_PATH>') You’ll just need to replace <REPLACE_WITH_YOUR_DATASET_PATH> with any existing dataset reference from your environment…

Hi @Vismay Barot , You might want to check the data type of the Sales Representative Location column. It looks like you’ve applied a filter value of 18, which is numeric, but the column might actually be stored as text. That type mismatch is likely causing the ORA-01722: invalid number error.

Thank you for sharing both the approaches! I’ll go with the first one — the workaround where you calculate the difference between the earliest and the latest date before applying the rank. I think this should address what I was trying to achieve in data flows. Appreciate your help!

Happy to help! 😊

Hi @Gianni Ceresa, Thanks for checking — yes, I’d like the RANK to start with 1, as my intent is to filter on Rank = 1 to keep only the rows with the earliest date.

Thanks for the details. The formula is correct, but seeing 2 for person 2333 likely means there are duplicate rows in the source table. This can happen if another column differs, even with the same person and assignment. I believe rewriting the formula to handle duplicates would be the right approach.

Hi, You can use the TOPN function to get the MAX date. It should do the trick! TOPN(CAST("SCST - Savings Impact Reporting"."Time"."Snapshot Date" AS DATE), 1) = 1 Let me know if you face any challenges while implementing it.

I think Data Flows internally use Logical SQL, and as far as I know, it doesn’t support the ORDER BY function directly — which might be why it’s not working as expected.

Hi,I do have a working solution for this, but it’s built using Data Flows. Would you like me to share the details of that approach?

Hi, Thank you for looking into this. I just gave it a try, but I’m getting the following error: Near <ORDER>: Syntax error [nQSError: 26012] Any idea what might be causing this? Thanks again for your help!

Hi @Raghavendra Raghunath-Oracle Thank you for the prompt response. Here is the snapshot of the formula I created named ‘Offers Accepted’. Could you please confirm if this looks correct? In this case, the requisition I filtered has 3 openings, and I can confirm all 3 positions are filled with the current phase and state as…

Hi, kudos on all these changes—especially the Openings Left to Fill metric, which was very much needed. At my organization, recruitment leadership also considers roles that are in the current phases 'Offer 'and state -'Accepted,' as well as applications under 'Post-Offer Checks' and 'HR' (excluding rejected or withdrawn…