Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date Formats in Set Analysis Expression

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!

1 Solution

Accepted Solutions
Not applicable
Author

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)

View solution in original post

4 Replies
swuehl
MVP
MVP

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)

Not applicable
Author

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)

Not applicable
Author

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
MVP
MVP

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.