Topic
  • 23 replies
  • Latest Post - ‏2013-02-04T20:58:56Z by SystemAdmin
SystemAdmin
SystemAdmin
1959 Posts

Pinned topic Using a date as a begining point for data analysis

‏2013-01-30T18:38:24Z |
Let me try and explaing what I am trying to do, then I will ask my question.

SHEET1
In the Attachment below (Sheet1), Column B is an expression as follows:

vEnrollment Date: CASE WHEN Description Field 1='WELLNESS' AND Description Field 2= 'FLYER' THEN (DateOfReferral) ELSE Cast(null as date) END

Then I set the Aggregate Function and the Roll Up Aggregate Function for this field as "Minimum".
I want the "vEnrollment Date" expression to show the Minimum Date ALWAYS, but only the minimum of data when Descristion Field 1 = 'WELLNESS' AND Description Field 2 = 'FLYER'.
So for John Doe, it would need to be Nov 13, 2012 in every row (for John Doe) in the vEnrollment Date column. and Sarah Doe would need to be Dec 10, 2012 in every row (for Sarah Doe) in the vEnrollment Date column.
Am I looking at this incorrectly? I need this because I am then going to be making a Summary Sheet.

VISION SHEET1
In the Summary Sheet (see Vision Sheet1) I have 2 more expressions:

vCalls 30 Days < Enrollment Date: If (DateOfReferral between _add_days(vEnrollment Date,-30) and vEnrollment Date) Then (1) Else (0)
vCalls 30 Days >= Enrollment Date: If (DateOfReferral between _add_days(vEnrollment Date,-1) and (_add_days(vEnrollment Date,+30))) Then (1) Else (0)

Then I set the Aggregate Function and the Roll Up Aggregate Function for these fields as "Total".
Currently the expressions are not pulling in any of the data where the vEnrollment Date expression is not working correctly. It only totals when there is an Enrollment Date.
I need both of these expressions to count the instances between 30 days before/after the enrollment date.

Please help! :)
Updated on 2013-02-04T20:58:56Z at 2013-02-04T20:58:56Z by SystemAdmin
  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-01-31T14:37:12Z  
    There is a lot of information here and it would be nice to have something to actually work with in the form of a sample.

    Are you looking for the minimum(date for Desc1,Desc2,Client Name)?
  • SystemAdmin
    SystemAdmin
    1959 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-01-31T16:08:12Z  
    There is a lot of information here and it would be nice to have something to actually work with in the form of a sample.

    Are you looking for the minimum(date for Desc1,Desc2,Client Name)?
    Daniel
    Yes. I want the Minimum(Date for (Desc1="criteria" And Desc2="criteria2"))
    When I try this, the Expression takes, but when running the report get a LONG error. Let me know if you would like the error :).
    How would I get you a form sample to work with?
    Thanks again!
  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-01-31T16:38:58Z  
    Daniel
    Yes. I want the Minimum(Date for (Desc1="criteria" And Desc2="criteria2"))
    When I try this, the Expression takes, but when running the report get a LONG error. Let me know if you would like the error :).
    How would I get you a form sample to work with?
    Thanks again!
    Sure, please post the error message and the expression you are using to cause it. (exact syntax pls)
  • SystemAdmin
    SystemAdmin
    1959 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-01-31T17:09:42Z  
    Sure, please post the error message and the expression you are using to cause it. (exact syntax pls)
    exact syntax: Minimum(DateOfReferral for (Description='CRISIS TO WELLNESS SERVICES' AND Description1='FREQUENT USERS'))

    Error when running the report:
    An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
    Details:
    UDA-SQL-0358 Line 7: Syntax error near "=".RSV-SRV-0042 Trace back:RSReportService.cpp(826): QFException: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(259): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(789): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(250): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(842): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_RequestExecution/RSRenderExecution.cpp(625): QFException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(290): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79): QFException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(357): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(179): QFException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(307): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(303): QFException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(307): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(175): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(307): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(137): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(357): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSAssembly.cpp(660): QFException: CCL_RETHROW: RSAssembly::createListIteratorAssembly/RSAssembly.cpp(712): QFException: CCL_RETHROW: RSAssembly::createListIteratorRSQueryMgr.cpp(1088): QFException: CCL_RETHROW: RSQueryMgr::getListIteratorRSQueryMgr.cpp(1164): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(1324): QFException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgr.cpp(1624): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommandRSQueryMgr.cpp(1614): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(189): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSQueryMgrExecutionHandlerImpl.cpp(173): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1147): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1145): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1102): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1078): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(788): QFException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(213): QFException: CCL_RETHROW: QFSQuery::Execute v2CoordinationQFSQuery.cpp(4452): QFException: CCL_THROW: CoordinationPlanner
  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-01-31T17:28:05Z  
    exact syntax: Minimum(DateOfReferral for (Description='CRISIS TO WELLNESS SERVICES' AND Description1='FREQUENT USERS'))

    Error when running the report:
    An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
    Details:
    UDA-SQL-0358 Line 7: Syntax error near "=".RSV-SRV-0042 Trace back:RSReportService.cpp(826): QFException: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(259): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(789): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(250): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(842): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_RequestExecution/RSRenderExecution.cpp(625): QFException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(290): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79): QFException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(357): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(179): QFException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(307): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(303): QFException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(307): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(175): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(307): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(137): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(357): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSAssembly.cpp(660): QFException: CCL_RETHROW: RSAssembly::createListIteratorAssembly/RSAssembly.cpp(712): QFException: CCL_RETHROW: RSAssembly::createListIteratorRSQueryMgr.cpp(1088): QFException: CCL_RETHROW: RSQueryMgr::getListIteratorRSQueryMgr.cpp(1164): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(1324): QFException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgr.cpp(1624): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommandRSQueryMgr.cpp(1614): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(189): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSQueryMgrExecutionHandlerImpl.cpp(173): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1147): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1145): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1102): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1078): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(788): QFException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(213): QFException: CCL_RETHROW: QFSQuery::Execute v2CoordinationQFSQuery.cpp(4452): QFException: CCL_THROW: CoordinationPlanner
    Your syntax is incorrect, hence the error. Can you try what I posted earlier to see what it gives you.

    
    minimum([Date] 
    
    for [Description1],[Description2],[ClientName])
    
  • SystemAdmin
    SystemAdmin
    1959 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-01-31T20:22:43Z  
    Your syntax is incorrect, hence the error. Can you try what I posted earlier to see what it gives you.

    <pre class="jive-pre"> minimum([Date] for [Description1],[Description2],[ClientName]) </pre>
    Daniel
    When I try this, I get a date for every row that does not minimize, nor is it unique to Desc1='Criteria1' and Desc2='Criteria2'
  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-02-01T01:42:12Z  
    Daniel
    When I try this, I get a date for every row that does not minimize, nor is it unique to Desc1='Criteria1' and Desc2='Criteria2'
    Hmmm.... I will have another look at this in the morning.
  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-02-01T14:50:05Z  
    Daniel
    When I try this, I get a date for every row that does not minimize, nor is it unique to Desc1='Criteria1' and Desc2='Criteria2'
    Please have a look at the attached PDF output and let me know if this is what you are looking for. If it is not, please explain how the output would have to change.
  • SystemAdmin
    SystemAdmin
    1959 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-02-01T17:36:38Z  
    Please have a look at the attached PDF output and let me know if this is what you are looking for. If it is not, please explain how the output would have to change.
    Daniel
    Take a look at the attachment. I Would need the minimum, but to show in all rows.
    Thanks for taking the time to help. I really appreciate it.
  • SystemAdmin
    SystemAdmin
    1959 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-02-01T17:39:22Z  
    Please have a look at the attached PDF output and let me know if this is what you are looking for. If it is not, please explain how the output would have to change.
    Let's see if the attachment works in this one.
  • SystemAdmin
    SystemAdmin
    1959 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-02-01T17:46:06Z  
    Test attachment
  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-02-01T17:49:57Z  
    Let's see if the attachment works in this one.
    Your attachment did not come through... hopefully mine will.

    Is this what you are looking for?
  • SystemAdmin
    SystemAdmin
    1959 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-02-01T20:21:22Z  
    Your attachment did not come through... hopefully mine will.

    Is this what you are looking for?
    Yes sir, that is perfect.
  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-02-01T21:08:10Z  
    Yes sir, that is perfect.
    I will post the spec on Monday morning, all I did on that one was a group on Client Name and then a calculation which reads:

    
    minimum([Date] 
    
    for [Client Name]
    
  • SystemAdmin
    SystemAdmin
    1959 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-02-01T21:30:35Z  
    I will post the spec on Monday morning, all I did on that one was a group on Client Name and then a calculation which reads:

    <pre class="jive-pre"> minimum([Date] for [Client Name] </pre>
    I don't understand, why wouldn't it pick up nov 7 or earlier for John Doe then?
  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-02-01T22:03:20Z  
    I don't understand, why wouldn't it pick up nov 7 or earlier for John Doe then?
    In the example I posted I used the calc on enrolement date... did you want it on referal date to pickup Nov 1st for John Doe?
  • SystemAdmin
    SystemAdmin
    1959 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-02-04T16:24:30Z  
    In the example I posted I used the calc on enrolement date... did you want it on referal date to pickup Nov 1st for John Doe?
    No I do not. The way you had it is perfect.
  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-02-04T17:46:27Z  
    No I do not. The way you had it is perfect.
    Awesome! Glad it worked out, as promised attached is the spec I used for the output.
  • SystemAdmin
    SystemAdmin
    1959 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-02-04T18:18:43Z  
    Awesome! Glad it worked out, as promised attached is the spec I used for the output.
    Thank you Daniel. Not to be difficult, but I am having a little trouble understanding this. Do you think you could explain what is going on, or do you have it in Standard SQL?
    Thanks
  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-02-04T18:30:27Z  
    Thank you Daniel. Not to be difficult, but I am having a little trouble understanding this. Do you think you could explain what is going on, or do you have it in Standard SQL?
    Thanks
    I am not sure I understand what you are asking ... the last report takes the minimum enrollment date within the client name grouping. This assigns the minimum value to all the rows of a client regardless of the description1 and descrption2. Although you stated this is what you want, it may not be as it ignores the two description field value....
    I would have figured you were looking for min(date for ClientName, Description1,description2)

    I am not sure I can explain it any different than the above.
  • SystemAdmin
    SystemAdmin
    1959 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-02-04T18:59:25Z  
    I am not sure I understand what you are asking ... the last report takes the minimum enrollment date within the client name grouping. This assigns the minimum value to all the rows of a client regardless of the description1 and descrption2. Although you stated this is what you want, it may not be as it ignores the two description field value....
    I would have figured you were looking for min(date for ClientName, Description1,description2)

    I am not sure I can explain it any different than the above.
    For further explanation I was referring to the .txt file you attached. In the example .pdf you provided it looks as though the minimum date field is conditional based on the Description 1 and Description 2 field.
    Let me explain: The vEnrollment Date field is taking the minimum Date Of Referral when Description Field 1='WELLNESS' AND Description Field 2='FLYER'.
    So is your MinDate field then taking the Minimum(vEnrollment Date)?
  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-02-04T20:08:47Z  
    For further explanation I was referring to the .txt file you attached. In the example .pdf you provided it looks as though the minimum date field is conditional based on the Description 1 and Description 2 field.
    Let me explain: The vEnrollment Date field is taking the minimum Date Of Referral when Description Field 1='WELLNESS' AND Description Field 2='FLYER'.
    So is your MinDate field then taking the Minimum(vEnrollment Date)?
    Yes in the example it takes your pre-cacluated minimum on referal date and take another min of it for the client name grouping. Please keep in mind that I just loaded your excel spreadsheet into a database to be able to look at this. In doing so your if then else values would already be caculated for for me..(instead of being done in the report.)
  • SystemAdmin
    SystemAdmin
    1959 Posts

    Re: Using a date as a begining point for data analysis

    ‏2013-02-04T20:58:56Z  
    Yes in the example it takes your pre-cacluated minimum on referal date and take another min of it for the client name grouping. Please keep in mind that I just loaded your excel spreadsheet into a database to be able to look at this. In doing so your if then else values would already be caculated for for me..(instead of being done in the report.)
    You're awesome. Just tried minimum(vEnrollment Date for Client name) and it worked perfectly. Thank you Daniel!