Variable to filter all ID's that are present in both periods

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…

image

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”.

image

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…

image

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

1 Like

… or
instead of merging the two queries, you can set a query-filter in the first query with "Result from another query"

1 Like

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;

  • I use the number of ID’s as a measure and don’t know how to do this in the query screen.
  • I make my queries in the report part where you do this in the stage before that
  • You merge in the Query part but no idea how to do this in report.

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

image

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