6 Replies Latest reply: Jun 6, 2016 12:10 AM by Shamit Shah

# Expression with date limitation

Hi,

I am trying to calculate the number of orders raised after the date today() -90 days and tried using the following expressions, but not getting any result:

Count(Distinct {<OrderDate = { "<=date(today())-90,'DD-MM-YYYY'))" }>}PurchaseOrder)

Count(Distinct {<OrderDate = { "<=date(today())-90))" }>}PurchaseOrder)

Not sure what's incorrect in the above expression.

Any ideas?

Thanks

Shamit

• ###### Re: Expression with date limitation

Try this: Count(Distinct {<OrderDate = {"<=\$(=date(today()-90,'DD-MM-YYYY'))" }>}PurchaseOrder)

• ###### Re: Expression with date limitation

Hi Gysbert,

I tried your suggestion, but it is giving me the total entire number of orders.

I also tried using > (greater than) thinking that I had got it wrong, but that also gave me the entire number of orders.

Count(Distinct {<OrderDate = {">=\$(=date(today()-90,'DD-MM-YYYY'))" }>}PurchaseOrder)

Thanks

Shamit

• ###### Re: Expression with date limitation

Check that your OrderDate field really contains date values and not text values that merely look as dates. See this blog post: Why don’t my dates work?

Also, remember that the set of the set analysis expression is calculated at the chart level, not at the row level. So if you're using OrderDate as a dimension in your chart then you won't get the result you probably expect.

• ###### Re: Expression with date limitation

Hi Gysbert,

I tried the following which works (i.e. orders after a certain date) but not ideal:

Count(Distinct {<,OrderDate = { ">=01/03/2016" }>}PurchaseOrder)

Thanks

Shamit

• ###### Re: Expression with date limitation

Ok, so if your date format is MM/DD/YYYY (or is it DD/MM/YYYY?) then why do you think using DD-MM-YYYY will work in the date() function? Try using the exact same date format: Count(Distinct {<OrderDate = {"<=\$(=date(today()-90,'MM/DD/YYYY'))"}>}PurchaseOrder)

• ###### Re: Expression with date limitation

I am using DD-MM-YYYY format.

Count(Distinct {<OrderDate = {"<=\$(=date(today()-90,'DD/MM/YYYY'))"}>}PurchaseOrder) works fine.

Thanks

Shamit