Help with set analysis "Date range"

[#1]

I need to create a report to show the different between the "total number of Sales" and "total number of sales the customer participate".

My Format is :

• For the total number of sales within date range: =Count({1<[Status ID]= {3,4}, [Type ID] = {1}, [Sales Date] = {">=$(=Date($(vFromDate)))<=$(=Date($(vToDate)))"}>} DISTINCT [Sales ID])
• For the total of sales selected customer participate: =Count({$<[Status ID]= {3,4}, [Type ID] = {1}, [Run Date] = {">=$(=Date($(vFromDate)))<=$(=Date($(vToDate)))"} >} DISTINCT [Sales ID])

but both of them are not working....

Can someone help me on this???

[#2]

Hi,

For showing total sales irrespective of customer try this expression

Count({<[Status ID]= {3,4}, [Type ID] = {1}, [Sales Date] = {">=$(=Date($(vFromDate)))<=$(=Date($(vToDate)))"},Customer=>} DISTINCT [Sales ID])

Or Post your sample file

[#3]

I use Count({1}. So all the selections will be ignore.

The problem should be caused by the Date range. I remove the date range part it is working...

[vivientexier]

Try to use numeric date. Set analysis does not handle well date format.

Create a "Sales_date_num" in your script (by multiplying your "Sales Date" by 1).

[Sales Date] * 1 as Sales_date_num

Be sure your variable use also numeric format.

[jagan]

Hi,

Check this expression

=Count({1<[Status ID]= {3,4}, [Type ID] = {1}, [Sales Date] = {">=$(=Date(vFromDate))<=$(=Date(vToDate))"}>} DISTINCT [Sales ID])

If not working then check whether the format of dates are same, if not convert to the same format by using Date(date, formatspecifier).

check [Sales Date] and =Date((vFromDate))

Hope this helps you.

Regards,

Jagan.

[#6]

If date formats of [Sales Date], vFromDate and  vToDate are same (e.g."03/01/2011 13:12")

Then use date range {">=$(=vFromDate)<=$(=vToDate)"}.

If date formats of [Sales Date],vFromDate and vToDate are not same (e.g."vToDate &vFromDate are like =03/01/2011 13:12" and [Sales Date] like "03/01/2011')

Then use date range {">=$(=Date(vFromDate))<=$(=Date(vToDate))"}

Or let me know your Date format then I can provide you proper code.

[#7]

Thank you very much. You are right, Set analysis does not handle well the date format. But for my case, change of the load script is not a very good idea. I have far too many tables and other reports......

But this is still a good idea. Maybe I can try this next time~~

[#8 · Accepted solution]

Many thanks to Jagan and Rajkumar. I make some changes to my formula.

I found that my default format for [Sales Date] is DD/MM/YYYY so I changed the Date function to match this format:

• For the total number of sales within date range: =Count({1<[Status ID]= {3,4}, [Type ID] = {1}, [Sales Date] = {">=$(=Date(vFromDate, 'DD/MM/YYYY'))<=$(=Date(vToDate, 'DD/MM/YYYY'))"}>} DISTINCT [Sales ID])
• For the total of sales selected customer participate: =Count({$<[Status ID]= {3,4}, [Type ID] = {1}, [Run Date] = {">=$(=Date(vFromDate, 'DD/MM/YYYY'))<=$(=Date(vToDate, 'DD/MM/YYYY'))"} >} DISTINCT [Sales ID])