topic Set analysis issue. in QlikView

Set analysis issue.

Anonymous — Fri, 20 Dec 2013 11:32:37 GMT

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

Re: Set analysis issue.

Fri, 20 Dec 2013 11:36:06 GMT

can you try this.

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

Re: Set analysis issue.

israrkhan — Fri, 20 Dec 2013 11:42:04 GMT

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).

Re: Set analysis issue.

Anonymous — Fri, 20 Dec 2013 12:02:36 GMT

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

Re: Set analysis issue.

senpradip007 — Fri, 20 Dec 2013 12:24:42 GMT

Hi nitin,

Check the date format correctly.

Re: Set analysis issue.

Anonymous — Fri, 20 Dec 2013 16:07:08 GMT

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

Re: Set analysis issue.

Anonymous — Fri, 20 Dec 2013 16:24:52 GMT

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).