topic Re: How to do this with set-analysis? in QlikView

How to do this with set-analysis?

andy — Mon, 10 Jun 2013 13:06:56 GMT

Hi folks,

I'm struggling to get a set-analys expression right.

I have a date-island with DateDimPeriod as a column and the fact-table have lines valid between workstart and workend.

This if-statement does the job

=sum(if(category='A' and DateDimPeriod>=monthstart(workstart) and DateDimPeriod<=MonthEnd(workend),ValuePerPeriod))

but it takes a lot of resources and sometimes the computer hangs.

I have also tried with intervalmatch but with the real data that operation takes forever to complete due to syn-keys. I tried to join the syn-key away but failed.

Next I have tried with set-analysis but cannot get the syntax correct.

How shall a set-analysis expression look like to present the sum per month of the valid data?

/Andy

Re: How to do this with set-analysis?

Mon, 10 Jun 2013 13:12:02 GMT

try this

sum({<DateDimPeriod = {">=$(=MonthStart(workstart))<=$(=MonthEnd(workend))"},category={'A'}>}ValuePerPeriod)

Re: How to do this with set-analysis?

swuehl — Mon, 10 Jun 2013 13:15:41 GMT

A set expression is evaluated once per chart not per dimension line, please check also:

http://community.qlik.com/docs/DOC-1335

Have you read the latest design blog posts by Henric regarding matching dates to intervals?

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/12/reference-dates

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

Re: How to do this with set-analysis?

andy — Mon, 10 Jun 2013 14:46:19 GMT

Ah thanks for telling me that the set-analysis is evaluated once per chart. Second time I fall into that pitfall!

I shall read those articles tonight, interesting for sure.

Until then I found a workaround I used before to just blow the data with an outer join and then selecting the valid rows from that large resident.

/Andy

Re: How to do this with set-analysis?

andy — Mon, 10 Jun 2013 20:09:09 GMT

Hi again

I read the posts from Henric, they were just perfect!

As I had discovered the intervalmatch is not the tool for this job, it is better used when having events that shall be grouped into intervals.

The reference-dates post was spot on, ending up with the very same data model as my outer join approach posted earlier I will change to the while-solution since that will work better with large data-sets.

Thank you swuehl!

/Andy