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

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
    ACCEPTED ANSWER

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

    ‏2013-01-31T14:37:12Z  in response to SystemAdmin
    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
      ACCEPTED ANSWER

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

      ‏2013-01-31T16:08:12Z  in response to DanielWagemann
      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
        ACCEPTED ANSWER

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

        ‏2013-01-31T16:38:58Z  in response to SystemAdmin
        Sure, please post the error message and the expression you are using to cause it. (exact syntax pls)
        • SystemAdmin
          SystemAdmin
          1959 Posts
          ACCEPTED ANSWER

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

          ‏2013-01-31T17:09:42Z  in response to DanielWagemann
          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
            ACCEPTED ANSWER

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

            ‏2013-01-31T17:28:05Z  in response to SystemAdmin
            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
              ACCEPTED ANSWER

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

              ‏2013-01-31T20:22:43Z  in response to DanielWagemann
              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
                ACCEPTED ANSWER

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

                ‏2013-02-01T01:42:12Z  in response to SystemAdmin
                Hmmm.... I will have another look at this in the morning.
              • DanielWagemann
                DanielWagemann
                850 Posts
                ACCEPTED ANSWER

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

                ‏2013-02-01T14:50:05Z  in response to SystemAdmin
                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
                  ACCEPTED ANSWER

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

                  ‏2013-02-01T17:36:38Z  in response to DanielWagemann
                  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
                  ACCEPTED ANSWER

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

                  ‏2013-02-01T17:39:22Z  in response to DanielWagemann
                  Let's see if the attachment works in this one.
                  • DanielWagemann
                    DanielWagemann
                    850 Posts
                    ACCEPTED ANSWER

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

                    ‏2013-02-01T17:49:57Z  in response to SystemAdmin
                    Your attachment did not come through... hopefully mine will.

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

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

                      ‏2013-02-01T20:21:22Z  in response to DanielWagemann
                      Yes sir, that is perfect.
                      • DanielWagemann
                        DanielWagemann
                        850 Posts
                        ACCEPTED ANSWER

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

                        ‏2013-02-01T21:08:10Z  in response to SystemAdmin
                        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
                          ACCEPTED ANSWER

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

                          ‏2013-02-01T21:30:35Z  in response to DanielWagemann
                          I don't understand, why wouldn't it pick up nov 7 or earlier for John Doe then?
                          • DanielWagemann
                            DanielWagemann
                            850 Posts
                            ACCEPTED ANSWER

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

                            ‏2013-02-01T22:03:20Z  in response to SystemAdmin
                            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
                              ACCEPTED ANSWER

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

                              ‏2013-02-04T16:24:30Z  in response to DanielWagemann
                              No I do not. The way you had it is perfect.
                              • DanielWagemann
                                DanielWagemann
                                850 Posts
                                ACCEPTED ANSWER

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

                                ‏2013-02-04T17:46:27Z  in response to SystemAdmin
                                Awesome! Glad it worked out, as promised attached is the spec I used for the output.
                                • SystemAdmin
                                  SystemAdmin
                                  1959 Posts
                                  ACCEPTED ANSWER

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

                                  ‏2013-02-04T18:18:43Z  in response to DanielWagemann
                                  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
                                    ACCEPTED ANSWER

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

                                    ‏2013-02-04T18:30:27Z  in response to SystemAdmin
                                    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
                                      ACCEPTED ANSWER

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

                                      ‏2013-02-04T18:59:25Z  in response to DanielWagemann
                                      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
                                        ACCEPTED ANSWER

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

                                        ‏2013-02-04T20:08:47Z  in response to SystemAdmin
                                        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
    ACCEPTED ANSWER

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

    ‏2013-02-01T17:46:06Z  in response to SystemAdmin
    Test attachment