topic Re: Expression as set analysis value in QlikView

Expression as set analysis value

Wed, 23 Nov 2011 20:34:35 GMT

Hello, i have the following expression in a qlikview chart

Sum({<MES_AÑO={'Oct-11'}>} TOTAL FLG_CRO), where MES_AÑO is the chart dimension,

and i need to replace the constant 'Oct-11' by the following expression: SubField(MES_AÑO, '-', 1) & '-' & (SubField(MES_AÑO, '-', 2) - 1)

I tried several options but none worked, any idea?

Expression as set analysis value

Wed, 23 Nov 2011 21:23:46 GMT

I notice that you are trying get the sum of the field FLG_CRO in same month of the previous year of the dimension MES_AÑO. Have you a complete calendar with the fields of month and year related of the dimension MES_AÑO? If this is the case I think that you can get the same result with a expresion like:

Sum({< [year] = {$(=Max([year])-1)} >} FLG_CRO)

Re: Expression as set analysis value

Miguel_Angel_Baeyens — Wed, 23 Nov 2011 22:04:53 GMT

Hi,

It would do creating a master calendar with a flag field for the current year (or month) and last year (or last year-month) instead of playing with string fields and functions (which will degrade performance if the chart aggreagates some thousands of records). Take a look at this application.

If you have a date field that stores a date timestamp, for example, 23/11/2011 (DD/MM/YYYY), the following expression will also work

Sum({< DateField = {">=$(=AddYears(MonthStart(Today()), -1))<=$(=AddYears(MonthEnd(Today()), -1))"} >} TOTAL FLG_CRO)

Note that

Today() will return 23/11/2011 (will update everyday)
MonthStart() will return 01/01/2011 in the left part and 30/11/2011 in the right part of the range
AddYears() will return 01/01/2010 in the left part and 30/11/2010 in the right part of the range

Anyway, the master calendar is always a good idea, because you will be able to use it in any other chart to represent any date or time dimension.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Expression as set analysis value

perumal_41 — Thu, 24 Nov 2011 06:29:33 GMT

Hi,

Try This Method .I thing this method helpful for Your Query .if you have any query please reply .

Sum({<MES_AÑO={$(=concat(Chr(39)&SubField(MES_AÑO, '-', 1) & '-' & (SubField(MES_AÑO, '-', 2) - 1)&Chr(39),','))}>} TOTAL FLG_CRO)

Regards

Perumal

Re: Expression as set analysis value

Thu, 24 Nov 2011 13:22:10 GMT

Hello, thank’s for answering.

But is not working because the concat expression returns something like ‘Oct-10’, ‘Nov-10’,…etc and I need for each row of table, only the MES_ANIO value corresponding to same month and previous year.

De: Perumal A

Enviado el: jueves, 24 de noviembre de 2011 3:30

Para: jrondineau

Asunto: - Re: Expression as set analysis value

<http://community.qlik.com/index.jspa> QlikCommunity

Re: Expression as set analysis value

created by Perumal A <http://community.qlik.com/people/perumal_41> in Development (QlikView Desktop) - View the full <http://community.qlik.com/message/167675#167675> discussion

Re: Expression as set analysis value

Thu, 24 Nov 2011 14:28:23 GMT

Hello, thanks for answering.

The problem with this solution is that Max() always return the same value for all the records and I have a table with distinct years, and for each row of the table I need to calculate the previous year in the set analysis expression.

De: Claudio Lagoa

Enviado el: miércoles, 23 de noviembre de 2011 18:24

Para: jrondineau

Asunto: - Re: Expression as set analysis value

<http://community.qlik.com/index.jspa> QlikCommunity

Re: Expression as set analysis value

created by Claudio Lagoa <http://community.qlik.com/people/clagoa> in Development (QlikView Desktop) - View the full <http://community.qlik.com/message/167622#167622> discussion

Re: Expression as set analysis value

Thu, 24 Nov 2011 14:37:07 GMT

If you put in table as a dimension AÑO_MES and as experssion Max(AÑO) return the same year in all rows? That seems like there are no a unique relation between the dimension AÑO_MES and the dimension AÑO, is this right?

Can you upload a example in qwv?

Re: Expression as set analysis value

Thu, 24 Nov 2011 17:46:15 GMT

Ok, take a look at this example...

go to the sheet "example"

I need to calculate de column "Sales Same Month Last year" for every row in the Sales table as the sum of the sales in the same month of the last year (relative to the month and year of each row)

Thanks again

Re: Expression as set analysis value

kumarnatarajan — Thu, 13 Dec 2012 17:34:25 GMT

Try this code in your edit script

MapingTable:

Mapping LOAD

uniqueId & '-' & Num(MonthYear) as Id,

Sales

Resident Employee;

//*********************** GetDetails *********************

GetDetails:

Load

Id,

CurrentYear,

PrevYear

;

Load

uniqueId & '-' & Num(MonthYear) as Id,

ApplyMap('MapingTable',uniqueId & '-' & Num(MonthYear),0) as CurrentYear,

ApplyMap('MapingTable',uniqueId & '-' & Num(Date(MakeDate(Year(MonthYear)-1,Month(MonthYear)),'MMM-YYYY')),0) as PrevYear

Resident TableName;