topic Passing correct dates in set analysis in QlikView

Passing correct dates in set analysis

spsrk_84 — Thu, 10 Mar 2011 15:16:42 GMT

Guys,

I have converted my date column in a proper format in the edit script but the date value is passed as numbers in set analysis

i have tried using the date function inside set analysis but still the issue is not solved . Please suugest if i am missing something

and i am using QV10

Passing correct dates in set analysis

Oleg_Troyansky — Thu, 10 Mar 2011 15:25:45 GMT

Set analysis requires that the date field and the date value are formatted in exactly the same way. Date field's format is determined in the "Document Properties" - "Number" tab, and the date format of the value can be determined within the formula. For example:

{<MyDate = {">$(=date(vStartDate, 'MM/DD/YYYY'))"}>}

If you quote your expression, we could try to find where is the problem.

Passing correct dates in set analysis

spsrk_84 — Thu, 10 Mar 2011 16:06:09 GMT

Hi ,

Thanks for the reply i have the date format of the field set correctly in document properties

now the expression i am using is

"SUM({<DimCalendar.FullDate={$(#=ONLY(DATE(DimCalendar.FullDate,'MM/DD/YYYY')))}>}RPT_Portfolio_Exposure.LoanOutstanding) "

now if i select some values in the field "FullDate" dates its passed as 46012 eventhough i am giving date function inside

set analysis .Also i have applied the same date function in the script for the same field

Passing correct dates in set analysis

Oleg_Troyansky — Thu, 10 Mar 2011 16:15:02 GMT

2 things you can try:

1. Get rid of '#' - I think, '#' signifies numeric expression, which might cancel the effect of the date() formatting.

2. Try flipping between Only() and Date() - instead of using only(Date(...)), use Date(Only(...)), - again, without the # in the beginning.

On the other hand, I don't quite understand the point of this expression - your Set Analysis states that FULLDATE should be equal to the selected value of FULLDATE. The same data set can be defined without using any Set Analysis - it's the default. If you need to enforce that the single date is selected, you can add "Calculation Condition" with the customized message, requesting that the user selects a single date.

Am I missing something?

Passing correct dates in set analysis

spsrk_84 — Thu, 10 Mar 2011 16:22:31 GMT

Thanks a lot buddy i have removed the # and the problem is solved and regarding the other point i have posted a part of the expression but in actual i have to do some MTD ,YTD calcuation based on the date selection and that's y i am using set analysis