7 Replies Latest reply: Dec 2, 2016 5:17 AM by Sunny Talwar

Date functions in set expression

Mikko Sippola Aug 17, 2016 5:10 AM

Hi, I want to have a table which constantly shows recent sales, regardless of current selection. Currently I'm a bit lost at why these two below don't yield same results?

Sum(If(date > '2016-08-08', sales)) works fine

Sum({$< date = {"> '2016-08-08'"}>} sales) gives zero as the result

And next question would be how to use functions in set expression. Ideally I'd want to have something like

Sum({1< date = {"> Date(Interval(Today() - 7, 'D'))"}>} sales) but that gives zero as well.

Re: Date functions in set expression

Rohit Kumar Aug 17, 2016 8:02 AM (in response to Mikko Sippola)

Hi MIkko,

I think your date format is not right, Please check your date format with which you are matching as mine is working fine:
Sum({$< date = {"> '2016-08-08'"}>} sales)

If your question is now answered, Please flag the Correct Answer (via the big "Correct Answer" button near every post).
Like Show 0 Likes (0)

Actions
Re: Date functions in set expression

Sunny Talwar Aug 17, 2016 8:13 AM (in response to Mikko Sippola)

How about this:

Sum({<date = {"$(='>' & Date(WeekStart(Today(), -1), 'YYYY-MM-DD'))"}>} sales)

Also read here about Dates in Set Analysis
Like Show 1 Like (1)

Actions
- Re: Date functions in set expression
  
  Rajesh Kota Dec 1, 2016 6:12 PM (in response to Sunny Talwar )
  
  Many Thanks Sunny T...this post of yours came in handy/helped solve a date related issue.
  
  Like Show 1 Like (1)
  
  Actions
  - Re: Date functions in set expression
    
    Sunny Talwar Dec 2, 2016 5:17 AM (in response to Rajesh Kota)
    
    Awesome!! I am glad it did
    
    Like Show 0 Likes (0)
    
    Actions
Re: Date functions in set expression

Mikko Sippola Aug 17, 2016 9:16 AM (in response to Mikko Sippola)

Sum({<date = {"$(='>' & Date(WeekStart(Today(), -1), 'YYYY-MM-DD'))"}>} sales)

That worked, thanks Sunny!

Rohit: Thanks for checking that out. I have these in Data load editor

SET TimeFormat='h:mm:ss TT';
SET DateFormat='YYYY-MM-DD';
SET TimestampFormat='YYYY-MM-DD h:mm:ss[.fff] TT';

so I would assume it should compare those dates correctly?

An arbitraty date with

Sum({$< date = {"$(='>' & Date('2016-08-08', YYYY-MM-DD))"}>} sales)

works though, so the problem definitely is somewhere within the date formating.
Like Show 0 Likes (0)

Actions
- Re: Date functions in set expression
  
  Sunny Talwar Aug 17, 2016 4:17 PM (in response to Mikko Sippola)
  
  If you got what you wanted, I would request you to close this thread by marking the correct responses and any helpful responses.
  Qlik Community Tip: Marking Replies as Correct or Helpful
  
  Best,
  Sunny
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Date functions in set expression
    
    Rohit Kumar Aug 18, 2016 12:27 AM (in response to Sunny Talwar )
    
    Hi Sunny,
    Actually I have only Qlik View Server License not Publisher license, Can I do this activity with this ?
    
    I was looking at Qlik View sample application and there is a chart layout (Object Container )like there are three buttons(Cycle, Drill, Details by Year), Every Button linked with a different chart(Can be line or bar chart). you just need to click on any of the buttons,linked chart will appear on the basis of linking,
    Same type of layout works with Variable Extension in Qlik Sense but Problem here is you can link field with the variable button not any chart, Can you please help me , How to create Qlik View Object Container in Qlik Sense
    
    There is one more requirement, I have a field where URLs are coming with every record , When I view that column in Qlik view , I can click on that URL and it will open in default browser, Expression behind that column is
    
    '<url>www.qlikview.com'
    Same I want to achieve in Qlik Sense but <URL> key word is not working. Can you help me to know how to display URL in any column ?
    
    Like Show 0 Likes (0)
    
    Actions

Go to original post