8 Replies Latest reply: Dec 13, 2012 12:35 PM by Kumar Natarajan

Expression as set analysis value

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

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

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 qliktech@sgaur.hosted.jivesoftware.com

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

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

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

Try this code in your edit script

MapingTable:

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

Sales

Resident Employee;

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

GetDetails:

Id,

CurrentYear,

PrevYear

;

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;

• Re: Expression as set analysis value

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

Hi,