Hello.
Quite a long intro below - what I need to know is
How do I write the expression that needs help below? The answer to this may come down to can I even have a Set Analysis condition like '{<Start_Date={min(Date)}>}' where although both are dates one appears in one table and the other appears in another i.e. I am using another field to substitute in a date value
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Please find attached a simple QVF that hopefully demonstrates my question. It is a small subset of data of my much larger actual data set.
It has 2 tables; The transaction table has a 'Drop' (which is an online display advertising order), 'Date' and 'Imps' (which are online ad impressions). The 3 drops I have mention are displaying impressions each day for the first 14 days of January. If you take a closer look you will see 2 drops (1978911 and 1978912) started 'on time' on 1st Jan while the 3rd drop (1990092) started late on the 5th Jan.
The reference table contains the Drop again (this is the link between the tables) the Start_Date and the Imps_Sold.
In the QVF you will find 3 KPI boxes. One is simply the sum of impressions served from the transaction table. The 2 others are more interesting.
One is a bit of a cheat and is 'Imps Sold by Start Date Static'. The measure within is
sum({<Start_Date={'01/01/2017'}>}ImpsSold)
The obvious limitation is I want the '01/01/2017' date part to be dynamic and specifically I want it to relate to the minimum date selection of the date field in the transaction table.
One initial challenge I am trying to overcome is merely highlighting drops that have started on time. I have made an attempt of sorts in the second KPI box.
[EXPRESSIONS THAT NEEDS HELP] sum({<Start_Date={min(Date)}>}aggr(sum(ImpsSold),Drop,Start_Date))
What I am attempting to express here in words is
Take each Drop where min(Date) = Start_Date THEN sum the ImpsSold of these Drops
assume the min(date) selected is 1st Jan hence this should identify the 2 drops that started on time (1978911 and 1978912) and give the answer 210,000 + 120,000 = 330,000
