topic Re: Complex aggr in QlikView

Complex aggr

Anonymous — Thu, 16 Aug 2012 15:01:07 GMT

Hi,

i need an advise:

My table :

DATETIME ENERGY DEVICE

14/8/12 00:00 1 1

14/8/12 01:00 2 1

14/8/12 02:00 3 1

15/8/2012

16/8/2012 00:00 -1 1

16/08/12 01: 00 26 1

16/08/12 07: 00 35 1

There are many DEVICE

DATETIME ENERGY DEVICE

14/8/12 00:00 1 2

14/8/12 01:00 2 2

14/8/12 02:00 3 2

15/8/2012

16/8/2012 00:00 -1 2

16/08/12 01: 00 99 2

16/08/12 07: 00 110 2

i need

the SUM of (max(Energy)) of today

Sutraction

the SUM of (Min(ENERGY) where Min Energy >0 ) of today

all the device sum together.

In this case: DEVICE 1 max of today is 35 and min of today(>0) is 26 ---> 35-26=6

DEVICE 2 max of today is 110 and min of today(>0) is 99 ----> 110-99 = 11

FINAL RESULT : 11+6 = 17

Is this possible ?

Thanks

Re: Complex aggr

nstefaniuk — Thu, 16 Aug 2012 15:10:38 GMT

Can you split Datetime in 2 ? Date at time? Or create in script the Date with a floor(Datetime) ? Or (better) calculate a flag CURRENT_DATE (only if you refresh data everyday)

a graph with Device as dimension and expression:

max({$<DATE={"$(=Today())}"}>}ENERGY) - min({$<DATE={"$(=Today())}"}, ENERGY={">0"}>}ENERGY)

max({$<CURRENT_DATE={"Yes"}>}ENERGY) - min({$<CURRENT_DATE={"Yes"}, ENERGY={">0"}>}ENERGY)

Re: Complex aggr

jonathandienst — Thu, 16 Aug 2012 15:12:03 GMT

If I have understood you correctly, something like this expression should work (assuming there are no other dimensions not mentioned in your post)

Sum(Aggr(Max(ENERGY) - Min(If(ENERGY > 0, ENERGY, 0)), DATETIME, DEVICE))

Hope that helps

Jonathan

Re: Complex aggr

swuehl — Thu, 16 Aug 2012 15:18:18 GMT

Try

=sum(

aggr(

max({<Date = {'$(=today())'}>}ENERGY) - min({<ENERGY ={">0"},Date={'$(=today())'}>}ENERGY)

,DEVICE)

)

where I created Date field like

LOAD

...

date(daystart(DATETIME)) as Date

...

FROM ...;

Hope this helps,

Stefan

Re: Complex aggr

Anonymous — Thu, 16 Aug 2012 15:59:37 GMT

Can i change the expression using Date > Daystart (today()) ? If i'll do an error appeared

So must not change the date with the daystart

=sum(

aggr(

max({<Date > {'$(=today())'}>}ENERGY) - min({<ENERGY ={">0"},Date={'$(=today())'}>}ENERGY)

,DEVICE)

)

Re: Complex aggr

swuehl — Thu, 16 Aug 2012 16:03:35 GMT

You need to use the equal sign here, it's more like a set assignment than a comparision operator.

But you can probably use

{<Date = {">$(=today())'"}>}

i.e. an advanced search string (using double quotes) and a larger than operator in the search string.

Re: Complex aggr

Anonymous — Fri, 17 Aug 2012 09:22:19 GMT

i wrote

=sum(aggr( max({<DATETIME = {">$(=today())'"}>}[ENERGY YELDED]) - min({<[ENERGY YELDED] ={">0"},DATETIME= {">$(=today())'"}>}[ENERGY YELDED]),IDDEVICE))

but seems not work.

Is not possible just fix a value in dimension where i can make Qlik consider just the datetime > daystart(today())?

Re: Complex aggr

swuehl — Fri, 17 Aug 2012 09:28:42 GMT

I've noticed you are using the DATETIME field instead of the Date field. Field format is important in set analysis, so this could be a reason why it is not working.

You can use a calculated dimension to limit your Dates to dates in the future, but that will introduce another dimension, grouping your data by IDDEVICE and Date.

Is this what you want?

Re: Complex aggr

Anonymous — Fri, 17 Aug 2012 10:05:26 GMT

I have others field, DATETIME is diveded in YEAR,MONTH,DAY:

can i put in the expression YEAR = Year(Today()) , MONTH = Month(today()), DAY = Day(today()) ? or something like that ?

i'd like to avoid the creation of another dimension in the script

Re: Complex aggr

swuehl — Fri, 17 Aug 2012 10:08:53 GMT

You can format any date / time as you like using the QV date/time functions. If your DATETIME format is YYYYMMDD (please check the format codes in the Help), you can try something like

{<DATETIME = {">$(=Date(today(),'YYYYMMDD'))'"}>}