Alternative to Level-Based Formula in Aggregate Insight (Without Using MV)

[msinha8]

Hello,

I'm currently working on an Aggregate Insight where I need to compute the minimum Start Date per Tag_ID as part of the dimension section, not the measure. I’m exploring alternatives to a level-based formula such as:

min(start_date,groupBy(tag_id))

Note: The column logic is defined in the Business Schema.

Scenario:

Source Table (Table A):

Table A
Tag_ID	Tag_Line_ID	Start_Date	Employee_Id	Flag	Col C	Amount	Misc
X123	0	5/1/2025	E123		..	100
X123	1	5/2/2025	E123	ADD	..	200	100
X123	2	5/3/2025	E123	ADD	..	300	100
X123	3	5/4/2025	E123		..	200
X123	4	5/5/2025	E123	DEL	..	500	50
X123	5	5/6/2025	E123		..	700

Required Output: Aggregated Table with

Grouping Dimension	Values
Col A	<<some calc formula>>
Tag_ID	X123
Employee_Id	E123
First Start Date	5/1/2025
Col C	..

Measure	Values
Total Amount	2000
ADD_misc	200
DEL_Misc	50

Note:  The actual insight includes additional dimension and measure columns.

I was able to achieve the correct output when placing the Start Date column in the Measure field. However, this causes the Employee ID to shift to the top grouping level and reorders the Start Date as a measure—something we are trying to avoid.

Would appreciate any suggestions on how to keep the grouping intact while still computing the minimum Start Date per Tag_ID without relying on MVs.

Best regards,
MS

[Venkat]

Hi msinha,

Please find the below points.

Scenario:1
Incorporate the Date column(Minimum Aggregare function) into the insight to consider it as a dimension find below options.

i) Create a materialized view (MV) and join it with the base table. This would allow you to bring in the minimum Start Date per Tag_ID.
However, I understand this approach isn't something you're considering based on your notes.

ii)Create an Analyzer table, if that aligns with your requirements. This table could hold the minimum Date column, and you could then join it with the base table and subsequently bring it into the report's aggregate table Grouping dimensions.

iii) Create a new column directly within the SQL query of your table in Incorta. By utilizing a window function at the query level, this new column would contain the minimum Start Date for each Tag_ID, which you could then use.

Scenario:2 To consider it as a measure
i) If you are okay to keep it as a measure then you can keep it in measures tray of the Aggregate insight and apply Min aggregate property.
However, I understand this approach isn't something you're considering based on your notes.

ii) You can use level based measure on double data type column as per incorta docs.
So I believe you need to convert it  toChar--> double and apply min level based function and re apply int-->string -->date/parseDate
I havent checked this approach just putting my thought

Reference url# Built-in Functions → min
http://docs.incorta.com/latest/references-built-in-functions-aggregation-min

Please elaborate more, you have also mentioned as 'the column logic is defined in the Business Schema'. If you are looking something else.

Thanks
Venkat