Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
Not applicable

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?

8 Replies
Not applicable

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)

MVP
MVP

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

perumal_41
Valued Contributor II

Re: Expression as set analysis value

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

Not applicable

Re: Expression as set analysis value

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

Not applicable

Re: Expression as set analysis value

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

Not applicable

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?

Not applicable

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

kumarnatarajan
Valued Contributor

Re: Expression as set analysis value

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;

Community Browser