topic Interval on set analysis in QlikView

Interval on set analysis

n1ef5ng1 — Tue, 20 Dec 2016 11:06:39 GMT

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

shraddha_g — Tue, 20 Dec 2016 11:13:14 GMT

Calculate interval in script to create a field and then use that field in set analysis.

sum({<Region = {'Asia'},IntervalField = {"<121"}>}Amount)

Anil_Babu_Samineni — Tue, 20 Dec 2016 11:31:53 GMT

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)

marcus_sommer — Tue, 20 Dec 2016 12:08:19 GMT

You could try:

sum({<Region = {'Asia'}, Amount = {"=Interval(Date1-Date2,'mm')<121"} >} Amount)

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

sunny_talwar — Tue, 20 Dec 2016 12:10:27 GMT

May be this:

Sum({<Region = {'Asia'}>} If(Date1-Date2 < MakeTime(2, 1), Amount))

Create Date1-Date2 in script and then like this:

Date1-Date2 as IntervalField

Sum({<Region = {'Asia'}, IntervalField = {$(=121/(24*60))}>} Amount)