Comparing data with result of another aggregation

[rasmusnielsen]

Hi all,

My data model consists of date, station and discount. So, for any given date, a station has a discount.

In my app, the user can select Date1, Date2, Target discount and discount offset. These values are stored in variables. The target and offset are used for determining a discount range: target +/- offset.  In the screenshot, the discount range is 8-12 (target=10, offset= +/-2)

I have identified the number of stations that for Date1 has discounts that are within the discount boundary.

I'm using this expression in the "On Target" measure:

Count(
   Aggr(
      If(
         Avg({1<date={"$(vDate1)"}>}[discount]) >= ($(vTarget)-$(vOffset))
         and
         Avg({1<date={"$(vDate1)"}>}[discount]) <= ($(vTarget)+$(vOffset))
      , station)
   , station))

Now, I wish to count the number of stations that was in the discount range on Date1 AND has a discount on Date2 which is lower than it was on Date1. My resulting table looks like:

So, for the row with date=2018-01-02, I would expect "# lower" to be 1, since station S3 was within the discount range on Date1 AND its discount on Date2 is lower than it was on Date1.

I need some kind of AGGR magic here, but so far I have not been able to figure it out myself. I have also attached the app.

Hope someone can help! 🙂

Best Regards

Rasmus

[sunny_talwar · Accepted solution]

Try this

Count(
	Aggr(
    	If(
        	Avg(TOTAL <station> {1<date={"$(vDate1)"}>}[discount]) >= ($(vTarget)-$(vOffset))
    	    and
	        Avg(TOTAL <station> {1<date={"$(vDate1)"}>}[discount]) <= ($(vTarget)+$(vOffset))
            and
            Avg(TOTAL <station> {1<date={"$(vDate1)"}>}[discount]) > Avg({1<date={"$(vDate2)"}>}[discount])
        , station)
	, station, date)
)

[rasmusnielsen]

Must simpler than I had imagined!
Thanks 🙂