Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi Steven,
Can you try below expression?
Sum($ <{mDate = {">= StatusStartDate < StatusStartDate"} > (mDate - StatusStartDate))
using wizard.
Set Analysis Wizard for QlikView | qlikblog.at
Thanks
Hi Steven,
Can you please try out the below expression?
=IF(Interval(StatusEndDate-StatusStartDate,'D') >90,Interval(StatusEndDate-StatusStartDate,'D'),'<90Days').
Hope this helps.