Max(Date) with Set Analysis

[eduardo_dimperio]

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

[Digvijay_Singh]

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

)

[eduardo_dimperio]

Hi Digvijay,

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

[fernando_tonial]

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.

[eduardo_dimperio]

Hi Fernando!

Same result

[Digvijay_Singh]

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))

[eduardo_dimperio]

Hi Digvijay,

Not working, just null results

[eduardo_dimperio]

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

[agigliotti]

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) )

[eduardo_dimperio]

Hi Andrea, Still null values