Using min / max Aggregation for "DATE" in Lumira

[former_member269926]

Hi,

does anyone know how to implement the min/max aggregation function for columns of type "DATE"? In my case I need the youngest and the oldest date out of my column of type date.

I the documentation I found this:

I already tried to convert the date with "ToNumber(<param>)" function into a number format, but the result of doing this is initial. The other function "ToText(<param>)" is not working, because of a wrong data type ( number format is needed, when I remember correctly).

Any ideas?

Thank you very much.

Regards

Thorsten

[ashutosh_rastogi · Accepted solution]

You can create a Min (Date) custom calculation. Where Min is the aggregate function on a date.

However that would work when you have date in your Visualization context (feeds).

Regards,

Ashutosh

[former_member269926 · Accepted solution]

Hi Ashutosh,

your suggestion using custom calculation works with diagram type "crosstable" as you can see in my screenshot below.

My problem is that I want to show the min/max date as a type of label in my storyboard. So I have to choose a diagram type "Numeric Point". With this type of diagram I can only access to measures in a custom calculation.

What I want to create is something like this:

Because I do not have much space in my report I want to turn of the axis labels and show the time period with my two min/max aggregation "labels".

Thank you.

Best regards,

Thorsten

[ashutosh_rastogi · Accepted solution]

Hi Thorsten,

Custom calculations are not supported for Numeric Point charts or in Dynamic Texts and hence you will not be able to create these kind of labels today.

Only possible way I can think of is showing a cross tab with some formatting options at the top, however that may not look good.

Regards,

Ashutosh

[former_member155146 · Accepted solution]

Hi,

Create a measure using min/max as aggregation on date column in prepare/visualize room, Then add simple text in compose room, Use Dynamic Text to create calculation (under text properties),

select the measure which created in custom calculation dialog,

then it can be placed as a label in the chart.

Thanks

Pramila

[ashutosh_rastogi · Accepted solution]

Thanks Pramila ... this is good one, this would work well with Story and Page level filters, however will not work if you have viz level filters.

Regards,

Ashutosh

[former_member269926 · Accepted solution]

Hi Pramila,

thank you very much! This is exactly what I need!

Best regards,

Thorsten

[former_member269926]

Hi Rahul,

thank you for your reply.

I've already created a measure out of my "DATE" column. Setting "min" or "max" has no effect to the measure.

To check the values I used this new measure in a visualization. The value of the measure is always "initial". I only see a value when I change the aggregation type to "COUNT" (or COUNT DISTINCT).

I tried the same with a "normal" data type (not date) and it works like I expect - I got the "min" value and the "max" value if I choose the related type of aggregation.

Thank you again.

Best regards,

Thorsten

[former_member207736]

You need to use create measure on date column to create measure. Now u can set min or max aggregation to it

Let me know if it helped