15 Replies Latest reply: Mar 30, 2012 8:26 AM by Praveen Kumar

Syntax for including a date field in a expression

Praveen Kumar Mar 28, 2012 8:32 AM

Hi,

I want to write a expression which counts for a particular day,

for eg. count(sales) , in which i need a include a where condition as date like count(sales) where date = 'today' or date = 'quarter to date"

I need to know the syntax for the same.

Help required on a urgent basis.

Syntax for including a date field in a expression

DanieleC Mar 28, 2012 8:51 AM (in response to Praveen Kumar)

Hello,
try this:

count({$ <date = {'xyz'} >} sales)

where xyz is the date you desire; you need to insert xyz in the right format according to your source data (do some test).

Regards,
Daniele
Like Show 0 Likes (0)

Actions
Syntax for including a date field in a expression

jagan mohan rao appala Mar 28, 2012 9:06 AM (in response to Praveen Kumar)

Hi,

Try this

count({$ <date = {'$(=Today())'} >} sales)

It gets the count of Sales for current date.

Regards,
Jagan.
Like Show 0 Likes (0)

Actions
- Syntax for including a date field in a expression
  
  Sunil Chauhan Mar 28, 2012 10:12 AM (in response to jagan mohan rao appala )
  
  count({<date={'today','quarter to date'}>} sales)
  
  hope this helps
  
  Like Show 0 Likes (0)
  
  Actions
  - Syntax for including a date field in a expression
    
    Praveen Kumar Mar 30, 2012 6:37 AM (in response to Sunil Chauhan)
    
    Hi,
    
    If I use this expression
    
    count({$<dt={'$03/28/2012'}>} SURGEON)
    
    it counts the total no of values and not considering the date in the count.
    
    Any corrections in the syntax?
    
    Like Show 0 Likes (0)
    
    Actions
    - Syntax for including a date field in a expression
      
      Celambarasan Adhimulam Mar 30, 2012 6:40 AM (in response to Praveen Kumar)
      
      Hi,
      Try with this
      count({$<dt-={'03/28/2012'}>} SURGEON) it excludes the date(03/28/2012) given.
      
      Celambarasan
      
      Like Show 0 Likes (0)
      
      Actions
      - Syntax for including a date field in a expression
        
        Praveen Kumar Mar 30, 2012 6:59 AM (in response to Celambarasan Adhimulam )
        
        celambarasan,
        
        count({$<dt-={'03/28/2012'}>} SURGEON) , even this gives the total count. my dt field in database is in varchar format, is that creating a problem to us?
        
        Like Show 0 Likes (0)
        
        Actions
        
        Syntax for including a date field in a expression
        
        Celambarasan Adhimulam Mar 30, 2012 7:05 AM (in response to Praveen Kumar)
        
        Hi,
             Yes.you have to format the date while loading the table from the database.like Date(Date#(Field,Format))
             Format is in what format the field in database has may be like 'DD/MM/YYYY'
        
        Celambarasan
        
        Like Show 0 Likes (0)
        
        Actions
        
        Syntax for including a date field in a expression
        
        Praveen Kumar Mar 30, 2012 7:23 AM (in response to Celambarasan Adhimulam )
        
        hi,
        
        I use a sql statement like select * from table1;
        
        now how to include the above said statement? either in sql format or i need to follow the syntax as qlik view format.
        
        Like Show 0 Likes (0)
        
        Actions
        
        Syntax for including a date field in a expression
        
        Celambarasan Adhimulam Mar 30, 2012 7:33 AM (in response to Praveen Kumar)
        
        Hi,
             Yes you can apply precede load to it like
             Load
                  Field1,
                  Field2,
                  Date(Date#(Field,Format)) as dateField;
              select * from table1;
        
        Celambarasan
        
        Like Show 0 Likes (0)
        
        Actions
        
        Syntax for including a date field in a expression
        
        Praveen Kumar Mar 30, 2012 8:26 AM (in response to Celambarasan Adhimulam )
        
        super cool!!!awesome!!! it works.
        
        Like Show 0 Likes (0)
        
        Actions
- Syntax for including a date field in a expression
  
  Praveen Kumar Mar 30, 2012 6:28 AM (in response to jagan mohan rao appala )
  
  Hi all,
  
  In the syntax mentioned, should i use the date as such or i should change it as per my database?
  
  eg, if it is dt, should i change the expression to
  count({<dt={'today','quarter to date'}>} sales)
  
  Like Show 0 Likes (0)
  
  Actions
  - Syntax for including a date field in a expression
    
    Celambarasan Adhimulam Mar 30, 2012 6:43 AM (in response to Praveen Kumar)
    
    Hi,
    you can use this for your requirement
    
    count({<dt={">=$(QuarterStart(Today()))<=$(Today())"}>} SURGEON)
    
    Celambarasan
    
    Like Show 0 Likes (0)
    
    Actions
    - Syntax for including a date field in a expression
      
      Praveen Kumar Mar 30, 2012 6:54 AM (in response to Celambarasan Adhimulam )
      
      celambarasan,
      
      It gives the total count and doesnt includes the condition in set analysis. Anything we are missing?
      
      Like Show 0 Likes (0)
      
      Actions
      - Syntax for including a date field in a expression
        
        Celambarasan Adhimulam Mar 30, 2012 6:59 AM (in response to Praveen Kumar)
        
        Hi,
        Try with distinct
        
        count({<dt={">=$(QuarterStart(Today()))<=$(Today())"}>} Distinct SURGEON)
        
        Celambarasan
        
        Like Show 0 Likes (0)
        
        Actions
        
        Syntax for including a date field in a expression
        
        Praveen Kumar Mar 30, 2012 7:04 AM (in response to Celambarasan Adhimulam )
        
        Still i use a distinct function, it gives only the total count and not for the particular date
        
        Like Show 0 Likes (0)
        
        Actions

Go to original post