topic Re: Set Analysis in QlikView

Set Analysis

Anonymous — Thu, 23 Jan 2014 06:49:38 GMT

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

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

Thu, 23 Jan 2014 07:28:47 GMT

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 — Thu, 23 Jan 2014 08:22:30 GMT

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.

Thu, 23 Jan 2014 08:29:27 GMT

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 — Thu, 23 Jan 2014 08:36:13 GMT

I have tested , it works fine.