Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

eduardo_dimperi
Valued Contributor II

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?

set_analysis.JPG

set_analysis2.JPG

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

Tags (1)
12 Replies
Digvijay_Singh
Honored Contributor III

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

)

eduardo_dimperi
Valued Contributor II

Re: Max(Date) with Set Analysis

Hi Digvijay,

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

fernando_tonial
Valued Contributor

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.

eduardo_dimperi
Valued Contributor II

Re: Max(Date) with Set Analysis

Hi Fernando!

Same result

set_analysis.JPG

set_analysis2.JPG

Digvijay_Singh
Honored Contributor III

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

eduardo_dimperi
Valued Contributor II

Re: Max(Date) with Set Analysis

Hi Digvijay,

Not working, just null results

eduardo_dimperi
Valued Contributor II

Re: Max(Date) with Set Analysis

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

agigliotti
Honored Contributor II

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

eduardo_dimperi
Valued Contributor II

Re: Max(Date) with Set Analysis

Hi Andrea, Still null values

Community Browser