Expression with date limitation

[shamitshah]

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

[Gysbert_Wassenaar]

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

[shamitshah]

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

[Gysbert_Wassenaar]

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.

[shamitshah]

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

[Gysbert_Wassenaar · Accepted solution]

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)

[shamitshah]

I am using DD-MM-YYYY format.

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

Thanks

Shamit