12 Replies Latest reply: Mar 8, 2018 10:04 AM by Eduardo DImperio

# Max(Date)  with Set Analysis

Hi,

I need to get the difference between consume of first and last month from each neighborhood, my problem is that some neighborhood have less months than others, but in set analysis it gets the max (date) of all neighbors and not individual,

ex this two pictures without selection and with selection. How do i do to fix that?

I have this code:

(

(SUM({<DATA_EXTRACAO  = {"\$(=DATE(MAX(DATA_EXTRACAO)))"},CONS0={">=0"},GROUP_TYPE_METER={"AGUA"}>} CONS0)-

SUM({<DATA_EXTRACAO  = {"\$(=DATE(MIN(DATA_EXTRACAO)))"},CONS0={">=0"},GROUP_TYPE_METER={"AGUA"}>} CONS0))

)

/

If(

SUM({<DATA_EXTRACAO  = {"\$(=DATE(MAX(DATA_EXTRACAO)))"},CONS0={">=0"},GROUP_TYPE_METER={"AGUA"}>} CONS0)>0,

SUM({<DATA_EXTRACAO  = {"\$(=DATE(MAX(DATA_EXTRACAO)))"},CONS0={">=0"},GROUP_TYPE_METER={"AGUA"}>} CONS0),1

)

Thanks

• ###### Re: Max(Date)  with Set Analysis

You may try with Aggr, not sure though

(

Aggr((SUM({<DATA_EXTRACAO  = {"\$(=DATE(MAX(DATA_EXTRACAO)))"},CONS0={">=0"},GROUP_TYPE_METER={"AGUA"}>} CONS0)-

SUM({<DATA_EXTRACAO  = {"\$(=DATE(MIN(DATA_EXTRACAO)))"},CONS0={">=0"},GROUP_TYPE_METER={"AGUA"}>} CONS0)),BAIRRO)

)

/

If(

Aggr(SUM({<DATA_EXTRACAO  = {"\$(=DATE(MAX(DATA_EXTRACAO)))"},CONS0={">=0"},GROUP_TYPE_METER={"AGUA"}>} CONS0),BAIRRO)>0,

Aggr(SUM({<DATA_EXTRACAO  = {"\$(=DATE(MAX(DATA_EXTRACAO)))"},CONS0={">=0"},GROUP_TYPE_METER={"AGUA"}>} CONS0),BAIRRO),1

)

• ###### Re: Max(Date)  with Set Analysis

Hi Digvijay,

I actually thought it would work, but unfortunately the results were the same

• ###### Re: Max(Date)  with Set Analysis

Hi Eduardo, you can try this:

(

(SUM({<DATA_EXTRACAO  = {"\$(=DATE(MAX(TOTAL <BAIRRO> DATA_EXTRACAO)))"},CONS0={">=0"},GROUP_TYPE_METER={"AGUA"}>} CONS0)-

SUM({<DATA_EXTRACAO  = {"\$(=DATE(MIN(TOTAL <BAIRRO>DATA_EXTRACAO)))"},CONS0={">=0"},GROUP_TYPE_METER={"AGUA"}>} CONS0))

)

/

If(

SUM({<DATA_EXTRACAO  = {"\$(=DATE(MAX(TOTAL <BAIRRO>DATA_EXTRACAO)))"},CONS0={">=0"},GROUP_TYPE_METER={"AGUA"}>} CONS0)>0,

SUM({<DATA_EXTRACAO  = {"\$(=DATE(MAX(TOTAL <BAIRRO>DATA_EXTRACAO)))"},CONS0={">=0"},GROUP_TYPE_METER={"AGUA"}>} CONS0),1

)

Don't worry, be Qlik.

Tonial.

• ###### Re: Max(Date)  with Set Analysis

Hi Fernando!

Same result

• ###### Re: Max(Date)  with Set Analysis

May be try with IF like this -

Sum(if(DATA_EXTRACAO = MAX(DATA_EXTRACAO and CONS0 >=0 and GROUP_TYPE_METER = "AGUA", CONS0)) -

Sum(if(DATA_EXTRACAO = MIN(DATA_EXTRACAO and CONS0 >=0 and GROUP_TYPE_METER = "AGUA", CONS0))

• ###### Re: Max(Date)  with Set Analysis

Hi Digvijay,

Not working, just null results

• ###### Re: Max(Date)  with Set Analysis

I think the Aggr() function will work, but something not right with the expression

• ###### Re: Max(Date)  with Set Analysis

let's try the below expression:

=Sum( if(DATA_EXTRACAO = Max(DATA_EXTRACAO) and CONS0 >=0 and GROUP_TYPE_METER = 'AGUA', CONS0, 0) )

-

Sum( if(DATA_EXTRACAO = Min(DATA_EXTRACAO) and CONS0 >=0 and GROUP_TYPE_METER = 'AGUA', CONS0, 0) )

• ###### Re: Max(Date)  with Set Analysis

Hi Andrea, Still null values

• ###### Re: Max(Date)  with Set Analysis

it's very strange, it should works or return 0 but not NULL.

• ###### Re: Max(Date)  with Set Analysis

I Agree with you

• ###### Re: Max(Date)  with Set Analysis

People, i tried this

(SUM({<DATA_EXTRACAO  = {"\$(=AGGR(DATE(MAX(DATA_EXTRACAO)),bairro))"},CONS0={">=0"},GROUP_TYPE_METER={"AGUA"}>} CONS0))

-

(SUM({<DATA_EXTRACAO  = {"\$(=AGGR(DATE(MIN(DATA_EXTRACAO)),bairro))"},CONS0={">=0"},GROUP_TYPE_METER={"AGUA"}>} CONS0))

But with this result: