Using a Dynamic Value in Modifier for Set Analysis

Fri, 17 Feb 2017 16:52:59 GMT

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

Re: Using a Dynamic Value in Modifier for Set Analysis

petter — Mon, 20 Feb 2017 01:08:14 GMT

Yes - You should be able to use a search condition that will find the matching rows:

Sum( {<Drop={"=Start_Date=Min(Date)"}>} ImpsSold)

Testing with you application gave me the answer you were looking for: 330,000

Re: Using a Dynamic Value in Modifier for Set Analysis

Mon, 20 Feb 2017 17:07:51 GMT

Thanks Petter,

I was unaware of that syntax, should come in very useful.

topic Re: Using a Dynamic Value in Modifier for Set Analysis in App Development

Using a Dynamic Value in Modifier for Set Analysis

Re: Using a Dynamic Value in Modifier for Set Analysis

Re: Using a Dynamic Value in Modifier for Set Analysis