Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Could someone please tell me if it is possible to use set analysis in this very simple sum example? Just trying to sum up Hours IF the HourDate is between Date1 and Date2. I am having trouble getting this to work on a large dataset where using If/Then is far too slow and not an option)
I am afraid this is impossible in set analysis, if it is, I would really appreciate suggestions!
THANK YOU!
If Date1 and Date2 are field names instead of variables then you probably won't get the results you want. The set is calculated per chart, not per row. See this document for an explanation: set_analysis_intra-record.qvw
If the fields are all in the same table you can create a field in the script that flags if the condition is true or false:
load Date1, Date2, HourDate, Hours, Project,
if(HourDate >=Date1 and HourDate<=Date2,1,0) as Flag
from ...
You can then use that flag field in the set analysis expression: sum({<Flag={1}>}Hours)
If Date1 and Date2 are field names instead of variables then you probably won't get the results you want. The set is calculated per chart, not per row. See this document for an explanation: set_analysis_intra-record.qvw
If the fields are all in the same table you can create a field in the script that flags if the condition is true or false:
load Date1, Date2, HourDate, Hours, Project,
if(HourDate >=Date1 and HourDate<=Date2,1,0) as Flag
from ...
You can then use that flag field in the set analysis expression: sum({<Flag={1}>}Hours)
Thanks, the referral to the example : set_analysis_intra-record.qvw did the trick! In this case I am able to use a unique index on the data rows so the Flag is not needed, although that's a great idea as well.
For the benefit of anyone coming along later, here is a picture of the final solution: