HELP. SET ANALYSIS

[Anonymous]

Hi,

I would need some help with a set analysis expression.

My source data is:

I have a variable named vFecha = 20150120

Would need an expression that calculates the sum of imp by DES_NEGOCIO (dimension in graph) for records that:

1. FLAG = 'A'
2. PK that have the max(FEC_MODIF) where FEC_MODIF<= vFecha

Selected records should be highlighted in yellow:

Thank you very much.

-----------------------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------------------------

Hola,

necesitaría ayuda con una expresión de set analysis.

Mis datos de origen son:

Tengo una variable llamada vFecha = 20150120

Necesitaria una expresion que calculase la suma de imp por DES_NEGOCIO (dimension en el grafico) para los registros que:

1. FLAG = 'A'
2. PK con la max(FEC_MODIF) donde FEC_MODIF<= vFecha

Los registros seleccionados deberían ser los resaltados en amarillo:

Muchas gracias.

[#2]

hope following code may help you

sum({<FLAG={'A'},PK=P({<FEC_MODIF={"<=$(=vFecha)"}>})>}IMP)

[anbu1984]

Can you post expected output

[Anonymous]

Thank you Bhawna Gupta, but I would need the PK with the max(FEC_MODIF <= vFecha)

anbu cheliyan, the output expected would be:

[avinashelite]

Hi Gaizka,

To achieve this follow the below steps.

1.First get the Max(FEC_MODIF) from the script (this cannot be easily achieved using set analysis)

try like:

Load *;

SQL select PK,

DES_NEGOCIO,

Flag,

max(FEC_MODIFY),

IMP from tablename group by

PK,

DES_NEGOCIO,

Flag,

IMP;

this will give the max modif date.

2. try the expression:

sum({<FLAG={'A'},PK=P({<FEC_MODIF={"<=$(=vFecha)"}>})>}IMP)

[Anonymous]

Hi Avinash,

the problem is that I cant know wich is the max(Fec_modif) in the script, because the variable vFecha is dinamic.

In the example the value for vFecha was 20150120 but it would be whatever the user select.

Thanks

[#7]

use this code

sum({<FLAG={'A'},PK=P({<FEC_MODIF={"<=$(=Max(FEC_MODIF))"}>})>}IMP)

it will show you the value for the max date as well as whenever user made any selection it will show the result for that set

[anbu1984]

Do you want to display PK with the max(FEC_MODIF <= vFecha) along with sum(IMP)?

[avinashelite]

try like this:

sum({<FLAG={'A'},PK=P({<FEC_MODIF={"=max(FEC_MODIF)"}>}if(FEC_MODIF<=$(vFecha),IMP))

[Anonymous]

it doesnt work Avinash