Object Definition in the universe

[former_member340306]

Hi,

I have a requirement where i need to count the request ID's based on Created date and Completed date e.g. on 1/1/2013 10 request ID's were created and 12 were closed . Following table shows how i want the results to be shown

Created Date         || Count(Request ID's created)    ||     Count (Request ID's completed)

Jan 2013                               3                                                10

Feb 2013                             10                                                 5

-------

----

Aug 2014                             20                                                50

Everything is coming from the same table and The way i am doing in the report is creating a Query 1 with "Request Created date field and count of ID's for  "request ID's created " and similarly   Query 2 with Request completed Date and Count of ID's for "request ID's completed" and merge the request created date and request completed date on report level to bring both count measures in one table and  it works fine.

But i want to know if it is possible to create a measure objects in the universe such as "Count(request ID's created)" and "Count(request ID's completed)" so i can just bring it together in one query and Count(request ID's created)  gets calculated using the created date

and Count(request ID's completed) gets calculated using the completed date and e.g. if i bring created date along with two measure as shown in the table above , it shows the right results so i dont have to do merge dimension and 2 queries on the report level !!!

At the end of the day i am trying to replicate something as follows in the universe :

/*

With 

Created_count AS ( Select created_date,  Count(ID)  as "ID created count " from Table1 group by Created_date),                  --subquery 1

Completed count AS ( Select completed_date,Count(ID) as "ID completed count " from Table1 group by Completed_date),     --- subquery 2

Select created_date, ID created count, ID completed count from Table 1 where created date=completed date

*/

Let me know if something like this is possible !

Thanks in advance,

Sheikh

[former_member190895]

Hi Ayub,

As the data is coming from the same table, you can have derived table in place and can create your objects. i.e. Create a Count(request ID's created) from original table and Count(request ID's completed) from derived table.

[former_member340306]

So let me break it down a little more --- In realtity i can bring both from the one main table -- just one by completed date and the other by created date and merge on the report level which works fine ---- but whats happening is that when i create a derived table e.g. for completed counts and i joined the derived table with the main table on ID (lowest grain) on the main table, now if i bring the closed ID count from the derived table and completed date from the main table numbers are good but if i bring the closed ID count with the created date from the main table numbers are wrong and that is happening because when i bring the counts with the created date they get calculated using created date not completed date and that brings me back to my origional question if i can some how define the object that it gets caluated using completed date regarless of being brought in with created or completed date.

Let me know if you need any further clarification.

Thanks,

Sheikh

[former_member190895]

Hi Ayub,

It would be better if you can share some screen-shot of the problems