Hi,
Not sure how to articulate my question using the right terms, so hope my attempt makes sense! I am looking to create a summary table where I want 1 row in 2 different line items. In the example, I would use Current Location as my first column then want to be able to show person a moved out of Maine and into Florida. This is similar to doing countifs in excel where it isn’t limited by the columns pulled in.
Is this possible to do in WEBI?
Thank you in advance for any help!
my quick&dirty solution would be a combined query or FHSQL with editing the SQL-statement
to a union of two separate IN- and OUT-lists and a dummy-counter-value
so with the result you can add a simple crosstab in WebI:
‘LOCATION’ in rows, ‘DESTINATION’ in columns, sum(COUNTER) in measure
something like this :
SELECT
“PERSON”,
“CURRENT_LOCATION” as LOCATION
‘IN’ as DESTINATION,
1 as COUNTER
FROM …
UNION
SELECT
“PERSON”,
“PREVIOUS_LOCATION” as LOCATION,
‘OUT’ as DESTINATION,
1 as COUNTER
FROM …
Really appreciate the quick response. Unfortunately I don’t have the ability to make updates by updating the SQL. I’m limited to build reports pulling in variables / creating variables. Not sure if there are any creative solutions with that limitation?
I would recommend you create two queries one with the Current Location and then other with the Previous Location. I mocked these two queries up with FHSQL and added a few more rows of data. You can create these queries using your universe; you do not need to manipulate any SQL for this solution. I also did a third query with both locations, but that isn’t necessary.
Now that you have your Current and Previous queries you need to merge them. To do that select the Current Location and Previous Location objects, right-click and choose Merge. Web Intelligence will name your merged object the same as whatever object you clicked on first. You can rename it by right-clicking on the newly create merged dimension and choose Edit Properties and changing the Merged Dimension Name to what you want. I changed mine to just “Location”.
Now we need variables to count the Ins and Outs. You need to add the optional “All” parameter for the Count functions so that all instances of a location are counted and not just the unique ones.
[Destination In]=Count([Current].[Current Location];All)
[Destination Out]=Count([Previous].[Previous Location];All)
Now I can crate a new table with the Location merged dimension and the two destination variables.
There you have it. If anything doesn’t make sense, please reply and I will try to clarify.
Noel
I got your approach to work too. If you have FHSQL access and skill, this is cleaner and simpler. No need for merging and variables.
Noel
Thanks! I think this might have done it
