Having trouble creating a field, maybe someone here can explain:
I need to create a time frame field, basically one that will only show days within the last 60 days when I use the field as opposed to the date field that pulls all the days from beginning of our sales. I imagine that the field will be calculated from the current date field, but just cannot figure out an expression that will do this. Any help is appreciated. Thank you!
In your load script you can create a field to flag the date values that are within the last 60 days. In your app you can then use a set expression to select based on this flag. The flag could be called IsLast60days and contain a 1 if the condition is true and 0 if not.
LOAD
....
SalesDate,
If (SalesDate >= Floor(Today(1))-60 , 1 , 0 ) AS IsLast60Days,
.....
In an expression in a chart/table you could sum the sales of the last 60 days like this:
Sum( {<IsLast60Days={1}>} Sales)
If you have one or more date related dimension you might also have to use the TOTAL qualifier:
Sum(TOTAL {<IsLast60Days={1}>} Sales)
or even
Sum(TOTAL <Country> {<IsLast60Days={1}>} Sales)
or if more than one dimension apart from the date related dimensions
Just share the way I use to do this, I think it's a little more flexible than set a flag like IsLast60days, because this flag could not reflect correctly if you make a selection on date field.
- As I usually ignore the weekends (Sat, Sun) when loading date dimension, I input a Dateorder to dimension in data load: such as:
[Dim_Date]:
load RowNo() as DateOrder, * where Exists(DimDateID);
SQL select DimDateID, [DateCode],
from [Dimension_Dates] where IsHoliday <> 'Y' and IsWeekend <> 'Y'
order by DimDateID;
Then on chart, I can use set analytics to easily get the list of date
