Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I have a set analysis with this expression
sum({<Region = {'Asia'}>}Amount)
I have an interval involved
Interval(Date1-Date2,'mm') < 121 |
Basically I need to calculate where the sum of amount for asia only where the difference between the two timestamp are < 121 mins
I def. need to do it all in one expression. cant do it in dimension, is there any way that i can do it.
Thanks
Calculate interval in script to create a field and then use that field in set analysis.
sum({<Region = {'Asia'},IntervalField = {"<121"}>}Amount)
You may try this, I hope you may have separate field for Interval in script
Then, you can use this
Load Region, Date1, Date2, Interval(Date1-Date,'mm') as DateDiff, Amount;
Load Region, Date1, Date2, Amount from DataSource;
sum({<Region = {'Asia'}, DateDiff = {"$(=Hour(DateDiff) < 121/(24*60))"}>} Amount)
You could try:
sum({<Region = {'Asia'}, Amount = {"=Interval(Date1-Date2,'mm')<121"} >} Amount)
or
if(Interval(Date1-Date2,'mm') < 121, sum({<Region = {'Asia'}>}Amount))
whereby it might be useful to do calculation-step within the script like suggested within the other answers.
- Marcus
May be this:
Sum({<Region = {'Asia'}>} If(Date1-Date2 < MakeTime(2, 1), Amount))
or
Create Date1-Date2 in script and then like this:
Date1-Date2 as IntervalField
Sum({<Region = {'Asia'}, IntervalField = {$(=121/(24*60))}>} Amount)