Hi,
I try to make a variable to filter all ID’s that are present in both periods.
The periods are variables i already made so basically something like below, only that clearly isnt right since it doesnt work:)
= ID Within [period1] And Within [period2]
I see two possibilities to solve this.
Two Queries and Merging
Build two queries so that each query returns only the data from each period. I will use the e-Fashion universe with Year as my period. And yes, my e-Fashion data is old.
Those queries yield the following results…
Now merge on the Store name. You would merge on your ID. Then create a variable based on the Year from my first query with the Qualification set to “Detail” and the Associated dimension set to the merged “Store name”.
Do the very same thing for the Year from the second query.
Next create a table with your merged dimension and filter on these two variables where they are not null.
There you have it. The Store name that is in both periods…
With this foundation you can also show the Store names that are in one period and not the other by changing the “Is not Null” filter on one of the variables to “Is Null”.
Subquery
This approach is more straightforward. It is all right here in the query. If you need to know how to create a subquery here is a start.
And here are the results…
If you want the Store names in one period and not the other you just need to adjust the “In List” operator for the Store name to “Not In List”.
Comparison
Both of these methods provide the same basic results with varied effort, complexity, flexibility, and format (one row per ID vs. one row per period). You choose whatever works best for you and most importantly what you understand.
Noel
… or
instead of merging the two queries, you can set a query-filter in the first query with "Result from another query"
Thanks all,
i’ll try this and come back on the result:)
I’n struggling a bit because in your query the measure sales revenue is used. In my casy the only measure is the ons i make myself and that is the aggregation of each ID. Don’t know how to make that variable in the query screen
I can’t figure it out.
2 things;
if your period can only be calculated within a variable in the report, then you have to merge these two variables.
if the period can be calculated in the source/universe, it will be easier to filter the data with the “Result from another query”.
after merging the two variables (or any other attributes) you can choose if you want to see only the matching data from both queries in your report (=inner join) or the data of query1 (=left join) or the data of query2 (=right join)
by using the different merge-elements
ok,.
Which variables did you use in the Student code?
“student code” was a random example/picture of how to do inner/left/right-joins in WebI
ID Within [period1] OR Within [period2]
is the right syntax
