topic Re: HELP. SET ANALYSIS in QlikView

HELP. SET ANALYSIS

Anonymous — Fri, 30 Jan 2015 08:37:52 GMT

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:

FLAG = 'A'
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:

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

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

Muchas gracias.

Re: HELP. SET ANALYSIS

Fri, 30 Jan 2015 09:18:38 GMT

hope following code may help you

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

Re: HELP. SET ANALYSIS

anbu1984 — Fri, 30 Jan 2015 09:21:37 GMT

Can you post expected output

Re: HELP. SET ANALYSIS

Anonymous — Fri, 30 Jan 2015 09:26:12 GMT

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

anbu cheliyan, the output expected would be:

Re: HELP. SET ANALYSIS

avinashelite — Fri, 30 Jan 2015 09:30:17 GMT

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)

Re: HELP. SET ANALYSIS

Anonymous — Fri, 30 Jan 2015 09:40:59 GMT

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

Re: HELP. SET ANALYSIS

Fri, 30 Jan 2015 09:46:13 GMT

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

Re: HELP. SET ANALYSIS

anbu1984 — Fri, 30 Jan 2015 09:49:24 GMT

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

Re: HELP. SET ANALYSIS

avinashelite — Fri, 30 Jan 2015 09:55:06 GMT

try like this:

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

Re: HELP. SET ANALYSIS

Anonymous — Fri, 30 Jan 2015 10:01:36 GMT

it doesnt work Avinash

Re: HELP. SET ANALYSIS

avinashelite — Fri, 30 Jan 2015 10:03:12 GMT

can you please provide the sample app ??? I did not tried with data

Re: HELP. SET ANALYSIS

Anonymous — Fri, 30 Jan 2015 10:36:48 GMT

The source and app

Re: HELP. SET ANALYSIS

avinashelite — Fri, 30 Jan 2015 11:30:56 GMT

Hi,

After lot of customization I got the Solution please find the attachment for the same

Re: HELP. SET ANALYSIS

Anonymous — Fri, 30 Jan 2015 11:42:48 GMT

Hi,

thank you very much but does not work if you change the variable vFecha

Re: HELP. SET ANALYSIS

avinashelite — Fri, 30 Jan 2015 11:50:50 GMT

sorry check this

Re: HELP. SET ANALYSIS

Anonymous — Fri, 30 Jan 2015 12:29:31 GMT

sorry but it doesnt work fine.

The solutions for two example should be:

Thank you

Re: HELP. SET ANALYSIS

Anonymous — Mon, 02 Feb 2015 07:54:24 GMT

Hi,

any suggestion about this?

Thank you very much

Re: HELP. SET ANALYSIS

avinashelite — Mon, 02 Feb 2015 08:29:03 GMT

Hi,

Sorry i was bit busy

try this expression this should work

if(FLAG='A' ,sum({<FEC_MODIF={"=Aggr(nodistinct max(FEC_MODIF),PK)"}>}IMP))

Re: HELP. SET ANALYSIS

avinashelite — Mon, 02 Feb 2015 09:38:35 GMT

Did it solve your issue??

If so please mark it as helpful or correct and close this thread

Re: HELP. SET ANALYSIS

Anonymous — Mon, 02 Feb 2015 09:55:59 GMT

Hi,

it is not working ok. It does not select only the max(FEC_MODIF) where FEC_MODIF <= vFecha. It is selecting all the records aggregated by PK where FEC_MODIF <= vFecha

For example, if you choose vFecha=20150118 the expression select also the record:

when the max(FEC_MODIF) where FEC_MODIF <= vFecha (20150118) is this one (and just it):

Anyway, thank you very much for the help.