Calculation error need urgent help

[#1]

Hi All,

I now ran into this problem,

I am looking in to past snapshots of data. We have a weekly refresh. My requirement is to find the count of all the items those were in Pending stage between this date range 05/24/2010 to 06/27/2010 has now( as of today) converted to 'Design In' or 'Design Win' Stage. DW_LOAD_DATE is the date field that is used

to store the snapshot every week.

I have attached the screenshot of the table box where it correctly shows whats happening, but when I use the following syntax it yields zero.

Count(if(DW_LOAD_DATE>='05/24/2010' AND DW_LOAD_DATE<=06/27/2010 AND LINE_ITEM_STATUS='Pending',

   if(U_LINE_ITEM_STATUS='Design In' or U_LINE_ITEM_STATUS='Design Win', OPP_ITEM)))

Could you please help me in finding where's the mistake is?

I have also attached the excel file exported from the QV app.

Thanks,

Bikash

[#2]

Hi All,

I found where's the mistake is but I need to fix it.

When I hard code the date it works but when I use two variables for the min(DW_LOAD_DATE) and max(DW_LOAD_DATE) it doesn't work.

The expression with the variables looks like this

=Count(distinct {<DW_LOAD_DATE={">=$(vConvStart)<=$(vConvEnd)"}, LINE_ITEM_STATUS={'Pending'}, U_LINE_ITEM_STATUS={'Design In', 'Design Win'}>} OPP_ITEM)

where $(vConvStart)=min(DW_LOAD_DATE) and $(vConvEnd)=max(DW_LOAD_DATE).

Please help,

Thanks,

Bikash

[#3]

Is load date being stored as a date or a character string?

Initially, I would say remove the leading zero on the dates and put 6/27/2010 in quotes.

You

[#4]

Hi Alang, Thanks for your response.

I recently discovered that its the set analysis expression which is actually not calculating the results correctly.

When I hard code, even with the leading zero it works fine. And Load Date is stored as date.

I need help in writing the correct expression for this set analysis expression.

=Count(distinct {<DW_LOAD_DATE={">=$(vConvStart)<=$(vConvEnd)"}, LINE_ITEM_STATUS={'Pending'}, U_LINE_ITEM_STATUS={'Design In', 'Design Win'}>} OPP_ITEM).

Instead if i write this it perfectly works,

=Count(distinct {<DW_LOAD_DATE={">=05/24/2010<=06/27/2010"}, LINE_ITEM_STATUS={'Pending'}, U_LINE_ITEM_STATUS={'Design In', 'Design Win'}>} OPP_ITEM)

where $(vConvStart)=min(DW_LOAD_DATE) and $(vConvEnd)=max(DW_LOAD_DATE).

NB*-

where $(vConvStart)=min(DW_LOAD_DATE) and $(vConvEnd)=max(DW_LOAD_DATE).

For some reason its not working.