Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two tables from separate QVDs auto joined in my presentation layer on a common 'Title' field.
Table 1:
Title
Format
Status
...other fields...
Table 2:
Title
Username
Sessions
...other fields...
Table 1 contains all of the records and Table 2 contains a subset of the records. I'm trying to get a set analysis expression for a chart straight table that will show all of the rows from Table 1 that do not have a corresponding record in Table 2. Since none of the records in Table 1 have a Username field, I thought something like this might work
=count({1-$<Username={"*"}>} Distinct Title)
However, I'm still only getting the records that are in both tables. I'd also like to only get the records with a Format="Cognos" and Status="Active"
Any help is appreciated.
Not sure which approach you are using. With Calculated Dimension you can do this:
If(IsNull(Username) and Format = 'Cognos' and Status = 'Active', Title)
With Set Analysis you can try this:
{<Title=e({<Username={'*?'}>})> * <Format = {'Cognos'}> * <Status = {'Active'}>}
Try this with a calculated dimension, may be like this:
If(IsNull(Username), Title) and check the option 'Suppress When Value Is Null' on dimension tabs under properties.
Attaching a sample to look at:
Best,
Sunny
Try
Count( {$<Title=E({1<Username={"*"}>} Title )>} distinct Title )
In other words: The E() function will return the set of Titles that are excluded if you select all Usernames.
HIC
Or if you want to use set analysis, you can try to use this:
Thanks, that seems to do it! Now how to solve for Format=Cognos and Status=Active?
Not sure which approach you are using. With Calculated Dimension you can do this:
If(IsNull(Username) and Format = 'Cognos' and Status = 'Active', Title)
With Set Analysis you can try this:
{<Title=e({<Username={'*?'}>})> * <Format = {'Cognos'}> * <Status = {'Active'}>}
The set analysis version works. Not sure why expression wouldn't work, but perhaps the field may have some value other than null.
The first one should have worked also, but I am glad that the set analysis option worked out for you.
Best,
Sunny