Set analysis issue.

[Anonymous]

I have a data where suppose there is actual_date, customer,

I also have other table where start date and complete date is present for every customer.

I have a common calendar also,

Now in a chart,

I need month as dimension(common month) and expression should be based on following condition.

count(customer) when,

actual_date >= monthstart(common month) and actual_date<(monthend(common month))

this type of more conditions are there...

Here I achieved the correct values of expression by using nested if conditions, but issue is as I am having very large amount of data , so performance issues are there.

Therefore I want to migrate it to set analysis.

I used

count({$<actual_date={">=$(=monthstart(min(common date))) <$(=monthend(min(common date)))"}>}distinct customer).

but by using it I am not able to achieve correct value.

Please help me out how to get correct values.

Regards

Nitin

[#2]

can you try this.

count({$<actual_date={">=$(=monthstart(min(common date)))}, actual_date={"<$(=monthend(min(common date)))"}>}distinct customer).

[israrkhan]

Min function will give you always min date in selection, both side.

try below:

count({$<actual_date={">=$(=monthstart(Min(commondate)))}, actual_date={"<=$(=monthend(=Max((common date)))"}>}distinct customer).

[Anonymous]

Actually, problem is ,  common calendar and Fact table are not linked and I want data monthly , i.e. based on Common Month,

By using commondate in set analysis, due to $ expansion, I use to get the minimum value of common date every time , but not according to the month of dimension,

Example>>

Min (commondate) is always clculated as 1-Jan-2012 in my case.

but as month is in dimension,

I should get as

Jan          1-Jan-2012

Feb          1-Feb-2012

.

.

.

.

regards

Nitin

[senpradip007]

Hi nitin,

Check the date format correctly.

[Anonymous]

Nitin,

Did you try InMonth() function?  It may be better in your case.  It may look like this:
count(if(InMonth(actual_date, common_date,0),customer))

Regards,
Michael

[Anonymous]

check whether the format in both actual_date and common date is same or not. If they are same,

try the following expression,

count({$<actual_date={">=$(=Date(monthstart(DATE#(min(common date), 'D-MMM-YYYY')), 'D-MMM-YYYY')) <$(=Date(monthend(DATE#(min(common date), 'D-MMM-YYYY')), 'D-MMM-YYYY'))"}>}distinct customer)

Because, sometimes if the left hand and right hand side of the expression doesn't show the same date format, the set analysis doesn't work at all and just show the count(distinct customer).