Topic
  • 3 replies
  • Latest Post - ‏2013-02-01T01:59:37Z by DanielWagemann
kgks
kgks
29 Posts

Pinned topic Running for Previous day

‏2013-01-31T19:49:44Z |
Hi,

Currently I have two reports, Report A runs from Tues - Friday and Report B runs only on Monday. Both the reports run the data for the previous day only. I would like to combine the reports into one. Currently one report i am using a calculation where

order date = current_date -1 for report A and current_date - 3 for Report B.

How do I combine into one so that it automatically runs for Friday, when it is scheduled on Monday?

Thanks
Updated on 2013-02-01T01:59:37Z at 2013-02-01T01:59:37Z by DanielWagemann
  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: Running for Previous day

    ‏2013-01-31T19:56:24Z  
    Try the following if you want the range:

    
    [OrderDate] between (add_days(current_date),-1) and (add_days(current_date),-3)
    


    Try the following if you want just the two values:
    
    [OrderDate] = (add_days(current_date),-1) OR [OrderDate] (add_days(current_date),-3)
    
  • kgks
    kgks
    29 Posts

    Re: Running for Previous day

    ‏2013-01-31T20:46:47Z  
    Try the following if you want the range:

    <pre class="jive-pre"> [OrderDate] between (add_days(current_date),-1) and (add_days(current_date),-3) </pre>

    Try the following if you want just the two values:
    <pre class="jive-pre"> [OrderDate] = (add_days(current_date),-1) OR [OrderDate] (add_days(current_date),-3) </pre>
    Thanks for the reply. Sorry if I misinterpreted the question. Basically I want the Report A to run on all business days, bringing back the data for the previous day. So when it runs on Monday, it should be Friday's data instead of Sunday's data.

    So I am thinking what exactly should be written in the filter. Shouldn't be identifying the day of the week and equate it to the sysdate?
  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: Running for Previous day

    ‏2013-02-01T01:59:37Z  
    • kgks
    • ‏2013-01-31T20:46:47Z
    Thanks for the reply. Sorry if I misinterpreted the question. Basically I want the Report A to run on all business days, bringing back the data for the previous day. So when it runs on Monday, it should be Friday's data instead of Sunday's data.

    So I am thinking what exactly should be written in the filter. Shouldn't be identifying the day of the week and equate it to the sysdate?
    OK that makes things a bit clearer... In that case you could try the following as a filter. The below is in theory as I have not tested it.... also it may not be the best answer... I personally don't like if then else in the boolean filter condition.
    
    [Date]= 
    
    if(day_of_week(current_date)=1) then (add_days(current_date,-3)) 
    
    else 
    
    if (day_of_week(current_date)=7) then ((add_days(current_date,-2)) 
    
    else (add_days(current_date,-1)