topic Re: Date Formats in Set Analysis Expression in QlikView

Date Formats in Set Analysis Expression

Mon, 08 Feb 2016 22:21:55 GMT

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!

Re: Date Formats in Set Analysis Expression

swuehl — Mon, 08 Feb 2016 22:52:48 GMT

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)

Re: Date Formats in Set Analysis Expression

Tue, 09 Feb 2016 02:50:37 GMT

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)

Re: Date Formats in Set Analysis Expression

Tue, 09 Feb 2016 16:51:57 GMT

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!

Re: Date Formats in Set Analysis Expression

swuehl — Tue, 09 Feb 2016 16:59:05 GMT

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.