Story Report Calculated Column finding the MAX

[maseitner]

Dear colleagues,

we need to find a way to identify the highest or latest value in a table by a formuala. In Excel you would pick the MAX formula BUT from what we tried in story report calculated column it is NOT Possible to use MAX formula.

Do you happen to have an idea here?

Thanks and BR

Martin

[JeroenA]

Hi Vicky, unfortunately that is not possible. Maybe, when you create a calculated column using the DAY_BETWEEN function with CURRENTDATE() and your Date field. The result will be a number. I expect you can then use that for the MIN or MAX function. Kind regards, Jeroen

[JeroenA · Accepted solution]

Hi maseitner,

I recommend using a crosstab table in your story. You can then create a calculation of type aggregation and use the MAX function. This should work.

Kind regards,

Jeroen

[maseitner · Accepted solution]

Hi Jeroen, thanks a lot for your idea. I will try that

[vickyjain9181 · Accepted solution]

Hi Jeroen,

[vickyjain9181 · Accepted solution]

Hi Jeroen, I need to show MAX value of a date . But the MAX function is only taking measures as input. But the date is defaulted as a dimension when the calculated field is added in query designer. Can you please let me know how to handle this? Thanks, Vicky.

[vickyjain9181 · Accepted solution]

Hi Jeroen, Thanks for taking time to reply me. Appreciate it very much. This is exactly what I am doing. I am just showing the MAX of number of days as a work around. Another followup question, is there a way to create a calculated dimension date field by adding the number of days to a date? I only see the option of calculated measures in the story report but I don 't find the option of calculated dimension. Any ideas or recommendations to arrive at the MAX date and show in the story? Thanks - Vicky