topic Re: Date Field in App Development

Date Field

Anonymous — Wed, 18 Jul 2018 18:14:20 GMT

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!

Re: Date Field

petter — Wed, 18 Jul 2018 21:44:22 GMT

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

Sum(TOTAL <Country,Region> {<IsLast60Days={1}>} Sales)

The last one could be read as "Get the total sum of all sales made within 60 days up to the level of Country and Region"

Re: Date Field

thi_pham — Thu, 19 Jul 2018 03:34:58 GMT

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

Sum({<DateOrder = {">=$(=Max(DateOrder)-60)"}>} Field)

Hope it help!