Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shamitshah
Partner - Creator
Partner - Creator

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

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)


talk is cheap, supply exceeds demand

View solution in original post

6 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
shamitshah
Partner - Creator
Partner - Creator
Author

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.


talk is cheap, supply exceeds demand
shamitshah
Partner - Creator
Partner - Creator
Author

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

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)


talk is cheap, supply exceeds demand
shamitshah
Partner - Creator
Partner - Creator
Author

I am using DD-MM-YYYY format.

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


Thanks

Shamit