Set analysis - urgent question

[rajeshvaswani77]

Hi,

     I have a expression like below

=Count({<[link_claim_Claim_Status]={'CLO'},[End Date] ={'>=$(=Date(only(varPreYearFullDateStart)<=$(=Date(varPreYearFullDateEnd))'}>} DISTINCT [ClaimID])

For now I have hardcoded the start and end dates in the variables

varPreYearFullDateStart=Num(Date('01/01/2010','dd/MM/YYYY'))

varPreYearFullDateEnd=Num(Date('31/12/2010','dd/MM/YYYY'))

The result i get somehow is not taking the date in between intervals.

Could someone kindly help me where I am going wrong?

Thanks in advance

Rajesh Vaswani

[kaushiknsolanki · Accepted solution]

Hi,

   Try after removing the data from the expression. Like

   =Count({<[link_claim_Claim_Status]={'CLO'},[End Date] ={'>=$(varPreYearFullDateStart)<=$(varPreYearFullDateEnd)'}>} DISTINCT [ClaimID])

Regards,

Kaushik Solanki

[rajeshvaswani77]

Thankyou somuch, it worked. What a silly mistake.

[Miguel_Angel_Baeyens]

Hello Rajesh,

First, when possible, do all transformation and formatting steps in the load script, so all your dates have the same format than the variables.

Note that in the format part of the Date() function you are using "dd" instead of "DD", and that the Date() function in the set analysis, as it has no format part, it will return regarding your DateFormat value (it's a variable created with your script any time you create a new document, with your regional settings, by default). So the question is does the  [End Date] field has the same format as the returned by Date(varPreYearFullDateEnd)?

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica