topic Re: set in QlikView

set

Anonymous — Mon, 11 May 2015 10:43:47 GMT

Hi all,

I am very new into Qlikview I want below statement in set analysis How to achieve it

Total no. of call logs where Category = SDS* , Month = open date and Resolve date = current month

count({....>}[Call Log ID])

Thanks

Re: set

mrossoit — Mon, 11 May 2015 11:00:40 GMT

Hi,

supposing Month is a numeric field and 'open date' is a possible value of [Resolve date] try this:

count({<Category = {'SDS*'}, [Resolve Date] = {'open date'}, Month = {'$(=num(month(today())))'}>} [Call Log ID])

Hope this helps

Regards,

Re: set

sagarkharpude — Mon, 11 May 2015 11:08:44 GMT

count({$< Category = {'SDS*'},Month = {'=$(vOpendate)'},Resolve date= {'=$(vCurrentmonth)'} >} [Call Log ID])

Crete 2 variables for vOpendate & vCurrentmonth

Re: set

Anonymous — Mon, 11 May 2015 11:47:11 GMT

When i tried i am getting error

=count({<Category={'SDS*'},Month={'=$(vDumpOpenDate)'}, [SSR Resolve Date]={'=$(vMaxMonth)'}>}[Call Log ID])

Re: set

Mon, 11 May 2015 11:50:18 GMT

What kind of error are you getting?

*Note: the format of Month and vDumpOpenDate variable value must be same and similar for SSR Resolve Date and vMaxMonth..

thanks,

Singh

Re: set

sagarkharpude — Mon, 11 May 2015 11:51:12 GMT

what are the values of vDumpOpenDate & vMaxMonth.

Are they matching with your fields Month & [SSR Resolve Date].

Re: set

Anonymous — Mon, 11 May 2015 11:54:51 GMT

yes both format are same i getting syntax error

Re: set

Anonymous — Mon, 11 May 2015 11:55:46 GMT

vDumpOpenDate=month(date(max([DumpOpen Date]),'MM'))

vMaxMonth=month(Date(Today(Date),'MM'))

Re: set

Mon, 11 May 2015 11:58:12 GMT

The set analysis expression looks good. Does it show Red line in the expression panel? Can you cross check the field names once again?

Also, we could put hard coded values for testing initally.

thanks,

Singh

Re: set

Anonymous — Mon, 11 May 2015 12:01:50 GMT

Re: set

Mon, 11 May 2015 12:15:12 GMT

I think you do not have a field name called Month. Can you cross check?

quick tip: avoid using built-in field names

Re: set

mrossoit — Mon, 11 May 2015 12:19:32 GMT

Upload your qvw. It will be easier and faster to help you.

Re: set

Anonymous — Mon, 11 May 2015 12:26:14 GMT

PFA...

Re: set

Mon, 11 May 2015 12:32:03 GMT

Try:

=count({<Category={'SDS*'}, MonthName={"=$(vDumpOpenDate)"}, [SSR Resolve Date]={"=$(vMaxMonth)"}>}[Call Log ID])

Re: set

Mon, 11 May 2015 12:33:48 GMT

This is giving 0 as result, because for MonthName = Mar you have no SSR Resolve Date for the month of May.

Re: set

Anonymous — Mon, 11 May 2015 12:35:32 GMT

{"=$(vDumpOpenDate)"} why we used "" here

can we use '' ?

Re: set

Anonymous — Mon, 11 May 2015 12:39:57 GMT

but SSR Resolve Date i have data for Mar then y its getting 0

Re: set

Mon, 11 May 2015 12:40:22 GMT

Single quotes are used as literals, so for evaluation we use double quotes.

for more details check: A Primer on Set Analysis

Hope this was helpful or correct.

Thanks,

Singh

Re: set

Mon, 11 May 2015 12:41:48 GMT

You have data for Mar but the variable you used is : vMaxMonth which has May as the value. That is why you are getting zero.