Set Analysis help

[#1]

Hi,

I've read the posts in the forum but I can't figure out how to write a specific Set Analysis.

I have attached a file showing the tables that I need to use in the Set Analysis and how they are connected.

I need to count the number if wipIDmail where;

WIPShortageNewdate > today()+5

And

Not(isnull([Released WO])

A WIPShortageNewdate can be entered more then once for a wipIDmail so we only wnat to look at the latest WIPShortageTimestamp. Perhaps with firstsortedvalue(WIPShortageNewdate,WIPShortageTimestamp).

I've tried to use this but it doesn't work;

count({1<wipShortageTimestamp={"=firstsortedvalue(wipShortageTimestamp,-wipShortageTimestamp)"},WIPShortageNewdate={"<(today()+5)"}>}DISTINCT wipIDmail)

wipShortageTimestamp={"=firstsortedvalue(wipShortageTimestamp,-wipShortageTimestamp)"} to sort out only the latest timestamp
WIPShortageNewdate={"<(today()+5)"} to sort out the newdate criteria

I don't know how to approach Not(isnull([Released WO]).

Thanks,
Jonas

[#2]

Your WIPShortageNewdate is probably not working, because the +5 changes it to a numeric date. Use Date() to get it back to date format. You may also need a format string if the format of that field is different from the default.

WIPShortageNewdate={"<$(=Date(today()+5))"}

The Not Null is harder. Set Analysis does not work very smoothly with nulls. My suggestion is to replace the null values of Released WO in your load (with something like -).

If(IsNull([Released WO]), '-', [Released WO]) As [Released WO]

Another option is a flag when the value is null, also handled in the load.

If(IsNull([Released WO]), 1, 0) As [Released WO Null]

[#3]

Thanks for your help! Actually it was the WIPShortageNewdate that wasn't in date format

But i still don't get the correct numbers.

I have attached a table that shows the values I want to evaluate.

count({1<WIPShortageNewdate={">$(=Date(today()+5))"}>}DISTINCT wipIDmail) = 5 but if I look in the table the result shoudl be 4.

count({1<wipShortageTimestamp={"=firstsortedvalue(wipShortageTimestamp,-wipShortageTimestamp)"}>}DISTINCT wipIDmail) = 20 but if I do the same analysis in the attached table I get the value 30.

I'm not sure if I can use the If(IsNull([Released WO]), '-', [Released WO]) As [Released WO] approach

The data is from different tables (see data structure in previous). The [Released WO] is Null because the WO that the shortage was logged against has been completed and thus the [Released WO] number is no longer part of the [Released WO] table. If this is possible would appreciate and example of the Load and Set Analysis for this.

I could perhaps join the two tables but I'd rather not as I want to do a EDX Partial Reload when a WIPShortageNewdate is logged.

Any ideas why I get the wrong result?

Jonas

[#4]

I put that first expression into an app using the Excel data provided and I get 4. I did have to use the Date() function to get WIPShortageNewdate into my date format. I've attached the sample.

As for the Null issue, I see what you're saying. You can't really change the null field, because it is a lack of a link that it is making it null. You could probably still add a null flag field. Could you give me your current syntax for loading those two tables? I think you should be able to get a field to use here without joining the two tables.