Date Formats in Set Analysis Expression

[#1]

Hello all -

I have a date field that I'm working with using set analysis, but I'm having a few issues. The date is formatted in the data as "YYYY-MM-DD" and is read in the field ReportDate. My set expression is like this:

=Sum({$<MetricName={"MyMetric"}, ReportDate={">=Max(ReportDate)-7"}>} Measure)

This doesn't work because subtracting 7 from the Max(ReportDate) turns the ReportDate into a numerical value. I've also tried:

=Sum({$<MetricName={"MyMetric"}, ReportDate={">=Date(Max(ReportDate)-7, 'YYYY-MM-DD')"}>} Measure)

and

=Sum({$<MetricName={"MyMetric"}, ReportDate={">=$(Date(Max(ReportDate)-7, 'YYYY-MM-DD'))"}>} Measure)

and

=Sum({$<MetricName={"MyMetric"}, ReportDate={">=$(=Date(Max(ReportDate)-7, 'YYYY-MM-DD'))"}>} Measure)

but none of these expressions evaluate correctly.

Am I doing something obviously wrong here? Thanks so much for the review!

[swuehl]

This should work:

=Sum({$<MetricName={"MyMetric"}, ReportDate={">=$(=Date(Max(ReportDate)-7, 'YYYY-MM-DD'))"}>} Measure)

Maybe you need to use the filter also in the Max() function?

=Sum({$<MetricName={"MyMetric"}, ReportDate={">=$(=Date(Max({<MetricName = {'MyMetric'}>} ReportDate)-7, 'YYYY-MM-DD'))"}>} Measure)

[#3 · Accepted solution]

The Max function will return the number values so you need to convert the number to your date format in SET analysis. For easy purpose create a variable that holds the Max date value with format and use the variable in SET analysis.

vMaxDate7 = Date(Max(ReportDate)-7,'YYYY-MM-DD')

If you need apply Metric Name filter on to date calculation,

vMaxDate7 = Date(Max({<MetricName={"MyMetric"}>}ReportDate)-7,'YYYY-MM-DD')

and use below expression:

Sum({<MetricName={"MyMetric"},ReportDate={">=$(vMaxDate7)"}>} Measure)

[#4]

Creating variables seemed to work! Not sure why it wouldn't work when coded directly in the expression, but I'm happy either way . Thank you!

[swuehl]

It should also work without the variable (though variables allow reuse, so it's not a bad idea).

I guess you just ran into some quoting troubles, note that I changed to single quotes for MetricName literal to prevent QV from misinterpreting an opening double quote as ending one.