topic Need Help on Set Analysis- date comparison without using variable in QlikView

Need Help on Set Analysis- date comparison without using variable

neetu_singh — Thu, 19 Dec 2013 11:42:13 GMT

Dear All,

I need your help in Set Analysis, as mention below m using the expression to calculate member count on the basis of date comparison, the syntax which i am using:

this expression is working fine if i select single month, but not working when i use month as a dimension as when i use month as dimension above mention "monthend or monthstart" expression takes only one value i.e. for jan start & end date. Because of this in my output reports it gives the value for jan in all rest of the month as mention below & highlighted in red, the value 128 is for jan only but its coming in all month:

Re: Need Help on Set Analysis- date comparison without using variable

vikasmahajan — Thu, 19 Dec 2013 11:44:57 GMT

Can you try pls

Count( Aggr( formula , Dimension))

where dimensions will be Months.

Regards

Vikas

Re: Need Help on Set Analysis- date comparison without using variable

sanketkhunte — Thu, 19 Dec 2013 11:48:49 GMT

Can you try same expression by using '1' instead of '$' ?

Re: Need Help on Set Analysis- date comparison without using variable

tresB — Thu, 19 Dec 2013 12:00:06 GMT

try something like:

Count ( <Your set analysis> Distinct Total <MonthField> MEM_USERID)

Re: Need Help on Set Analysis- date comparison without using variable

neetu_singh — Thu, 19 Dec 2013 12:32:03 GMT

Hello Tresesco,

What do you mean by Total <MonthField>

Can you explain..

Re: Re: Need Help on Set Analysis- date comparison without using variable

tresB — Thu, 19 Dec 2013 12:38:57 GMT

Hi,

Total <MonthField> would disregard your month dimension which is there in your chart. For better understanding, have a look at the attached sample. Hope that helps you understand.

Re: Need Help on Set Analysis- date comparison without using variable

neetu_singh — Thu, 19 Dec 2013 12:41:07 GMT

This doesnt work, Please chk & let me know

Re: Re: Need Help on Set Analysis- date comparison without using variable

tresB — Thu, 19 Dec 2013 12:50:36 GMT

Another point just noticed.

While you are using min()/max() functions, the similar set analysis has to be included there too, I guess. Could you please share example app with sample data?

Re: Need Help on Set Analysis- date comparison without using variable

shadoworth — Thu, 19 Dec 2013 12:57:40 GMT

You should use the " instead of ' when using set analysis with >=< sign.

For example you should write

ENG_ACTDATE={">=$(=MonthStart(Min(COMMON_DATE)))"}

instead of

ENG_ACTDATE={'>=$(=MonthStart(Min(COMMON_DATE)))'}

Try this to solve your problem. If this doesn't I will check the rest of the formula.

Re: Re: Re: Need Help on Set Analysis- date comparison without using variable

neetu_singh — Thu, 19 Dec 2013 15:38:24 GMT

Hi Tresesco,

please find the attached sample data with qvw. in a straight table there are two expression one is with Set Analysis & second one is with If Condition the output using if condition is correct and i want the same with set analysis.

Re: Re: Re: Need Help on Set Analysis- date comparison without using variable

shadoworth — Thu, 19 Dec 2013 16:17:19 GMT

I checked your example.

First there some error in syntax. The correct expression for the set analys is of your example is

COUNT({$<ACTDATE={">=$(=MONTHSTART(MIN([COMMON DATE])))<=$(=MONTHEND(MIN([COMMON DATE])))"},STARTDATE={"<$(=MONTHEND(MIN([COMMON DATE])))"},

COMPLETEDATE={">$(=MONTHEND(MIN([COMMON DATE])))"}>}DISTINCT USER)

where I changed ' in " and COMMON_DATE in [COMMON DATE]

Moreover there is a logical issue:

The expressions computed inside the set analysis (such as your $(=MONTHSTART(MIN([COMMON DATE]))) ) are performed by QLIK regardeless to the row of the table.

Hence for example $(=MONTHSTART(MIN([COMMON DATE])) is always valued as 1/1/2012.

I'm not sure that this is what you want.

Re: Need Help on Set Analysis- date comparison without using variable

neetu_singh — Thu, 19 Dec 2013 16:31:08 GMT

thats my issue that its working on selection but i want wrt the row of the table not with selection of month.

Re: Need Help on Set Analysis- date comparison without using variable

tresB — Thu, 19 Dec 2013 18:10:58 GMT

Hi Neetu,

Now it's clear to me what you are trying for.

Set analysis expression is executed ONCE for AN OBJECT, i.e. not row wise. That means for your case min/max values for date would be same(max/min of all months) for all the months rather than monthly min/max values. Hence you would not get the result similar to that of IF expression. Therefore, it's safe to say that, for such scenarios IF expression is the solution.

PFA, you would find what i mean.