Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
try this
sum({<DateDimPeriod = {">=$(=MonthStart(workstart))<=$(=MonthEnd(workend))"},category={'A'}>}ValuePerPeriod)
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
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
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