topic Re: Set Analysis Help in QlikView

Set Analysis Help

reddwarfcrew — Thu, 20 Sep 2018 21:08:00 GMT

Hi, I have a table of data structured as follows:

CaseID	Status	StatusStartDate	StatusEndDate
1	Submitted	01/01/2010	03/01/2010
1	Reviewed	03/01/2010	25/06/2010
1	Valid	25/06/2010	20/09/2018

I then have a variable populated on the sheet called 'mDate'. mDate is a point in time and I'm I need to aggregate the days in status based on the point in time chosen.

So my set Analysis has

Sum($ <{mDate = ">=StatusStartDate"}, {mDate = "<StatusEndDate"}> (mDate - StatusStartDate))

Now this works fine, but I want to additionally add in a criteria to only sum if the Days in Status is >90. I assumed this would work but doesn't

Sum($ <{mDate = ">=StatusStartDate"}, {mDate = "<StatusEndDate"}, {(mDate - StatusStartDate) = ">90"} > (mDate - StatusStartDate))

Any ideas?

Thanks

Re: Set Analysis Help

mikecrengland — Thu, 20 Sep 2018 22:03:07 GMT

Hi Steven -

I'm not sure that it's doing what you think... The syntax looks all wrong. What are you trying to sum? A simple example is:

sum( {<Year={2008}>} Sales )

which is summing sales where the year = 2008. Curly brackets around the set and the condition. Parenthesis around what you're trying to sum.

Set Analysis works once for the object, and not on a row by row basis. There's a .pdf out there named "QlikLearn-Set-Analysis-Presentation" that might help. It's attached to this link:

Re: I am Very new to Qlikview and planning to learn from scratch .

Mike

Re: Set Analysis Help

nsetty — Thu, 20 Sep 2018 22:24:17 GMT

Hi Steven,

Can you try below expression?

Sum($ <{mDate = {">= StatusStartDate < StatusStartDate"} > (mDate - StatusStartDate))

using wizard.

Set Analysis Wizard for QlikView | qlikblog.at

Thanks

Re: Set Analysis Help

logeshwariloki — Fri, 21 Sep 2018 07:23:15 GMT

Hi Steven,

Can you please try out the below expression?

=IF(Interval(StatusEndDate-StatusStartDate,'D') >90,Interval(StatusEndDate-StatusStartDate,'D'),'<90Days').

Hope this helps.