Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a dimension based on a date field. I want to show just the 7 highest values for that date fields ... NOT the top (highest) values for the measure which might not show the past 7 days. I want to show the past 7 days.
As it turned out, I didn't need the Only function. I was able to get the bar chart to show the running seven days (after some specific date time formatting) like this ...
The Date Dimension
=Date([DateField], 'M/D/YYYY')
and then ...
The Measures were like this:
=Sum({<[DateField]={">=$(=Date(Today() -7, 'MM/DD/YYYY hh:mm:ss TT'))"}>} [MyMeasureField])
This is really close to what the original reply and the follow up reply suggested.
If I read your post correctly, I think you are looking for something like this (you may have to adjust for date formats):
aggr(Only({$<[DateField]={"<=Date(Today())>(Date(Today()-7))"}>} [DateField]), [DateField] )
The set analysis {$<[DateField]={"<=Date(Today())>(Date(Today()-7))"}>} would also limit the data if you used it in your measure expression.
Thanks - I tried the first one (replacing all instances of Date Field with my actual Date Field) and it didn't work. Got this instead...
I will tried the other one as a set expression in my measure and that also didn't work.
Try with '$' expansion, ensure the date field format is same as Today() format else you would need to add the format as 2nd parameter in the date function -
aggr(Only({$<[DateField]={"<=$(=Date(Today()))>$(=(Date(Today())-7)"}>} [DateField]), [DateField] )
As it turned out, I didn't need the Only function. I was able to get the bar chart to show the running seven days (after some specific date time formatting) like this ...
The Date Dimension
=Date([DateField], 'M/D/YYYY')
and then ...
The Measures were like this:
=Sum({<[DateField]={">=$(=Date(Today() -7, 'MM/DD/YYYY hh:mm:ss TT'))"}>} [MyMeasureField])
This is really close to what the original reply and the follow up reply suggested.