topic Set Analysis - Beginner in QlikView

Set Analysis - Beginner

pkelly — Wed, 12 May 2010 09:11:53 GMT

Hi....

I've just recently upgraded to version 9 which means that I can start to use Set Analysis instead IF statements.

I have the following expression....

=sum(IF(%CalendarKey = varToday, soi_Sales))

How do I convert this to set analysis...?

I have tried..

=sum({<[%CalendarKey]={$(=varToday)}>} soi_Sales) /1000

QlikView doesn't report any error with the statement but my column just has blank entries.

Where am I going wrong...?

spsrk_84 — Wed, 12 May 2010 10:15:10 GMT

Hi,

Try like this

sum({$< [ %CalendarKey ] = varToday)>} soi_Sales) /1000

sum( { $ < [ %CalendarKey ] = { $(varToday) } > } soi_Sales) /1000

and one more thing check whether the varToday Variable contains any value or not since if it is null ,the result will be null...

Regards,

Ajay

spsrk_84 — Wed, 12 May 2010 10:18:36 GMT

Hi,

Forgot to say one more point check the format of data in both the fileds i.e in [ %CalendarKey ] and the varToday variable....

even if the foramts are matching it will result nulll

Regards,

Ajay

pkelly — Wed, 12 May 2010 12:23:50 GMT

Thanks Ajay

Think the second one is closest as the first is giving an error...

Still getting blanks though...

varToday has 12/05/2010 as its value (dd/mm/yyyy)

I do have records as this is looking at sales order intake...

Any help greatly appreciated...

Wed, 12 May 2010 13:09:51 GMT

assume a date format problem. Can you try to filter on a fixed date and play around with the formats you might be using , 12-05-2010, 2010-12-05 etc.

e.g. sum({<[%CalendarKey] = {'12/05/2010'}>} soi_Sales)