topic Set Analysis in QlikView

Set Analysis

Anonymous — Fri, 03 Mar 2017 13:45:06 GMT

Bom dia.

Tenho uma tabela que mostra um determinado valor referente a mar 2017, porém precisaria que essa coluna pegasse o mes da dimensão e calculasse o valor de 6 meses atrás...e isso se repetisse para os outro, tipo,

Jan 2017 iria calcular = jan2017 -180 dias

Fev 2017 iria calcular = fev2017 -180 dias

E assim por diante...

Tentei fazer usando set analysis, mas não muda nada:

SUM({<C1_CLAS_N4 = {'310101'} , MES_ANO = {"=MONTHNAME(MES_ANO - 180)"}>} VALOR)

Alguém sabe com fazer para eu buscar o valor da dimensão menos um numero de dias e dai sim somar o valor?

Re: Set Analysis

Anil_Babu_Samineni — Fri, 03 Mar 2017 13:48:36 GMT

Off course, That should work

Another approach

SUM({<C1_CLAS_N4 = {'310101'} , MES_ANO = {"$(=MONTHNAME(MES_ANO - 180))"}>} VALOR)

SUM({<C1_CLAS_N4 = {'310101'} , MES_ANO = {"$(=AddMonths(MonthName(MES_ANO),-6))"}>} VALOR)

Re: Set Analysis

Anonymous — Fri, 03 Mar 2017 14:03:44 GMT

I dont know ehy not works...=/

When i put this, the values turn 0...

Re: Set Analysis

Miguel_Angel_Baeyens — Fri, 03 Mar 2017 14:10:02 GMT

First, check what format the field MES_ANO has. Assuming it is created using the function MonthName(), then the expression should be

SUM({<C1_CLAS_N4 = {'310101'} , MES_ANO = {"$(=MonthName(AddMonths(MES_ANO, -6))"}>} VALOR)

If MES_ANO does not have the format MMM YY as returned by the function MonthName() you need to use the Date() function instead with the right mask to display as exactly the MES_ANO does.

Re: Set Analysis

Anonymous — Fri, 03 Mar 2017 14:47:26 GMT

Actually, i used MonthName(), but i tried any way, bu t not works...=/
I will attach a example...

Re: Set Analysis

Anonymous — Fri, 03 Mar 2017 18:47:18 GMT

Hi, attached there are a sample what i need. In second column i need it valeus be a same value reference six month ago...But set analysis don't work...if someone can help me...Thanks