Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

shamitshah
Not applicable

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Not applicable

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)

6 Replies
Gysbert_Wassenaar
Not applicable

Re: Expression with date limitation

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

shamitshah
Not applicable

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

Gysbert_Wassenaar
Not applicable

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.

shamitshah
Not applicable

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

Gysbert_Wassenaar
Not applicable

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)

shamitshah
Not applicable

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