Set Analysis

[Anonymous]

Suppose I have Three fields in a table

ID, Create_Date,End_Date,

And I have a common calendar,

These two tables are not joined ........

Now in chart, I want to have Year, Month, Day from MAstercalendar

as

I have created a drill down group of Year,Month,Day from mastercalendar and using this group as dimension.

nad I want to calculate two measures.

1,  count of ID wref Create_Date

2 count of ID wref End_Date.

What would be the proper Set analysis expression written.

Regards

Nitin

[#2]

Hi,

You can achieve this through alternate solution,

Just split tour table in two parts,

1. Id as IDC , Create_Date

2. Id as IDE, End Date

May Create date with End Date and make YMD group

Count ID of both tables in expression.

Alternatively, using same table and calendar master, solution is complex.

You need to write multiple expression for Create_Date and End_Date.

[Anonymous]

we can use if statement also without splitting in multiple tables.

as count(if(masterdate=create_date,id)) and

count(if(masterdate=end_date,id)).

But this solution is very slow.

so I want an optimized solution.

Splitting in multiple tables will not work for me as there are many date fields in my table.

[#4]

I think,

count(if(masterdate=create_date,id)) and

count(if(masterdate=end_date,id))

will not work if you use masterdate as dimension, and drill down functionality will not work.

In that case, i think you need to write, if(Cal_date='Jan', count({<create_month='Jan'>}id)).....etc..

But that would be complex and slow....

[Anonymous]

I have tested , it works fine.