topic Re: Static expressions in QlikView

Static expressions

mromrod22 — Wed, 25 Nov 2020 16:16:04 GMT

Hi community,

I need modify a expression of bar char, but not look the path.

I have this char, that works with this expression:

count(DISTINCT {<[Tipo Fecha]={$(vTipoFecha)}, Foto={'Publicación anual'}, [Revisión plan anual] = {'Rev0'}>} SOLI_ID_SOLICITUD) +

count(DISTINCT {<[Tipo Fecha]={$(vTipoFecha)}, Foto={'Publicación anual'}, [Revisión plan anual] = {'*'} - {'Rev0'}, ESSO_ID_ESTADO_BLOQ={1}>} SOLI_ID_SOLICITUD)

And this is my problem:

The results depending of filters, are these:

The numbers in red are that I need change. The results I want are these:

Explain with a example:

Filter -> "Rev0,Rev1,Rev2"

Static values for Rev0 in Ene and Feb, static values for Rev1 in Mar and Abr, and since May to Dic., values of Rev2.

Conclusion: static values for Revs checked and rest of values of the last Rev checked.

Any idea?

Thanks.

Re: Static expressions

mromrod22 — Thu, 26 Jan 2017 09:13:52 GMT

Forgot explain each Rev corresponds to two months, in order: Rev0 (Ene, Feb), Rev1 (Mar, Abr).........

Thanks

Re: Static expressions

mromrod22 — Thu, 26 Jan 2017 09:58:15 GMT

Also forgot show the dates that I have:

Thanks

Re: Static expressions

vinieme12 — Thu, 26 Jan 2017 11:03:21 GMT

please post a sample, it'll be easier to assist

Re: Static expressions

mromrod22 — Thu, 26 Jan 2017 12:08:48 GMT

Create a sample is difiicult (need three tables, many data...) , and can´t share the .qw, confidentially policy with client. Sorry.

Re: Static expressions

sunny_talwar — Thu, 26 Jan 2017 13:29:36 GMT

See if this can help you create a sample

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Re: Static expressions

mromrod22 — Thu, 26 Jan 2017 15:01:28 GMT

Ok, share the .qw scrambled and reduced.

Thanks. Wait your help.

Re: Static expressions

sunny_talwar — Thu, 26 Jan 2017 16:39:18 GMT

Try this expression:

If(GetSelectedCount([Selector Anual]) = 1 and Len(Trim([Selector Anual])) > 4,

count(DISTINCT {<[Tipo Fecha]={$(vTipoFecha)}, Foto={'Publicación anual'}, [Revisión plan anual] = {'Rev0'}>} SOLI_ID_SOLICITUD) +

count(DISTINCT {<[Tipo Fecha]={$(vTipoFecha)}, Foto={'Publicación anual'}, [Revisión plan anual] = {'*'} - {'Rev0'}, ESSO_ID_ESTADO_BLOQ={1}>} SOLI_ID_SOLICITUD),

If(GetSelectedCount([Selector Anual]) = 2,

Only(Aggr(If(RowNo() = NoOfRows(),

count(DISTINCT {<[Tipo Fecha]={$(vTipoFecha)}, Foto={'Publicación anual'}, [Revisión plan anual] = {'Rev0'}>} SOLI_ID_SOLICITUD) +

count(DISTINCT {<[Tipo Fecha]={$(vTipoFecha)}, Foto={'Publicación anual'}, [Revisión plan anual] = {'*'} - {'Rev0'}, ESSO_ID_ESTADO_BLOQ={1}>} SOLI_ID_SOLICITUD)), Mes, ([Selector Anual], (TEXT)))),

Only({<[Selector Anual]>}Aggr(If(RowNo() = NoOfRows(),

count(DISTINCT {<[Tipo Fecha]={$(vTipoFecha)}, Foto={'Publicación anual'}, [Revisión plan anual] = {'Rev0'}, [Selector Anual]>} SOLI_ID_SOLICITUD) +

count(DISTINCT {<[Tipo Fecha]={$(vTipoFecha)}, Foto={'Publicación anual'}, [Revisión plan anual] = {'*'} - {'Rev0'}, ESSO_ID_ESTADO_BLOQ={1}, [Selector Anual]>} SOLI_ID_SOLICITUD)), Mes, ([Selector Anual], (TEXT))))))

This will only work if you have QV12, if you don't then you have to sort the Selector Anual field in the script for this to work properly. I can help you with that if you don't have QV12, but if you do then check out the attachment

Re: Static expressions

mromrod22 — Fri, 27 Jan 2017 07:24:29 GMT

Hi,

My version is 11.20. Sorry, I´m beginner in Qlik, don´t know that you say with "sort the Selector Anual field in the script".

Your expression show errors and not works:

I proved in local with Qlik 12, and not show errors in expression, and works, but not completely good.

Example:

This expression is difficult for a beginner -_-''

Thanks.

Re: Static expressions

sunny_talwar — Fri, 27 Jan 2017 10:25:37 GMT

That is fixed, but there might be another combination which might not fixed. Since there are so many combinations, it tends to get tricky. But since you have a better idea, can you check....

Re: Static expressions

mromrod22 — Fri, 27 Jan 2017 12:14:57 GMT

Now works! But only in local. The version of server is 11.20, here not works; "allocated memory exceeded":

Don´t know why not recognize keywords after error?

Very thanks.

Re: Static expressions

sunny_talwar — Fri, 27 Jan 2017 12:19:04 GMT

This is because of this new functionality which was introduced in QlikView 12.

The sortable Aggr function is finally here!

Now to help you better here, I would need you to tell me about how you create Selector Anual field? Is it created like CurrentYear and CurrentYear-1 and Rev0 to Rev5 for both years? Or is there more to this then what I just described?

Re: Static expressions

mromrod22 — Fri, 27 Jan 2017 12:54:18 GMT

Selector Anual field is equal to AñoMesSelectorAnual or left(AñoMesSelectorAnual ,4), and AñoMesSelectorAnual is

SOIN_ANO& '@' & 'Rev'&ceil((SOIN_MES-1)/2)

Re: Static expressions

sunny_talwar — Fri, 27 Jan 2017 13:04:56 GMT

Since I don't have access to your data source, I will give your something which may or may not work.... but at least we can try. Add this table to your script

SortSelectorAnual:

LOAD DISTINCT

[Selector Anual],

RowNo() as Sort

Resident SelectorAnual

Order By [Selector Anual];

and change your expression to this:

If(GetSelectedCount([Selector Anual]) = 1 and Len(Trim([Selector Anual])) > 4,

count(DISTINCT {<[Tipo Fecha]={$(vTipoFecha)}, Foto={'Publicación anual'}, [Revisión plan anual] = {'Rev0'}>} SOLI_ID_SOLICITUD) +

count(DISTINCT {<[Tipo Fecha]={$(vTipoFecha)}, Foto={'Publicación anual'}, [Revisión plan anual] = {'*'} - {'Rev0'}, ESSO_ID_ESTADO_BLOQ={1}>} SOLI_ID_SOLICITUD),

If(Len(Trim([Selector Anual])) = 4,

Only({<[Selector Anual]>}Aggr(If(RowNo() = NoOfRows(),

count(DISTINCT {<[Tipo Fecha]={$(vTipoFecha)}, Foto={'Publicación anual'}, [Revisión plan anual] = {'Rev0'}, [Selector Anual]>} SOLI_ID_SOLICITUD) +

count(DISTINCT {<[Tipo Fecha]={$(vTipoFecha)}, Foto={'Publicación anual'}, [Revisión plan anual] = {'*'} - {'Rev0'}, ESSO_ID_ESTADO_BLOQ={1}, [Selector Anual]>} SOLI_ID_SOLICITUD)), Mes, Sort)),

Only(Aggr(If(RowNo() = NoOfRows(),

count(DISTINCT {<[Tipo Fecha]={$(vTipoFecha)}, Foto={'Publicación anual'}, [Revisión plan anual] = {'Rev0'}>} SOLI_ID_SOLICITUD) +

count(DISTINCT {<[Tipo Fecha]={$(vTipoFecha)}, Foto={'Publicación anual'}, [Revisión plan anual] = {'*'} - {'Rev0'}, ESSO_ID_ESTADO_BLOQ={1}>} SOLI_ID_SOLICITUD)), Mes, Sort))))

Again, I don't know if this will work or not, but worth giving a shot.

Best,

Sunny

Re: Static expressions

mromrod22 — Mon, 30 Jan 2017 11:53:59 GMT

Hi Sunny,

Now works perfectly, but not understand the logic of Only(Aggr...). What returns the only function? Can you explain me?

Very thanks, Correct answer for you

Re: Static expressions

sunny_talwar — Mon, 30 Jan 2017 11:56:46 GMT

Its been few days... I can't even remember why I did what I did.... I will take a look at this in the evening and get back to you

Re: Static expressions

mromrod22 — Mon, 30 Jan 2017 15:44:28 GMT

Ok, very thanks for your time.

Re: Static expressions

mromrod22 — Wed, 01 Feb 2017 07:03:59 GMT

Remember explain me, please!

Re: Static expressions

sunny_talwar — Thu, 02 Feb 2017 03:17:33 GMT

I guess I used it at two places... I think one of the places we can avoid using Only, but the other one still needs it. Let me put both those options here and explain them

Only({<[Selector Anual]>}Aggr(If(RowNo() = NoOfRows(),

count(DISTINCT {<[Tipo Fecha]={$(vTipoFecha)}, Foto={'Publicación anual'}, [Revisión plan anual] = {'Rev0'}, [Selector Anual]>} SOLI_ID_SOLICITUD) +

count(DISTINCT {<[Tipo Fecha]={$(vTipoFecha)}, Foto={'Publicación anual'}, [Revisión plan anual] = {'*'} - {'Rev0'}, ESSO_ID_ESTADO_BLOQ={1}, [Selector Anual]>} SOLI_ID_SOLICITUD)), Mes, Sort))

Only function is equivalent of not having any function, but it can only display something if there is one and only one thing to display. For instance, if you have a fieldname ABC which can display 1 or 2 when nothing is selected, only(ABC) will display null, whereas lets say after selections there is only one possible value. Now Only(ABC) will display that value. This is the same thing which will happen if you don't use the Only function. So, just ABC will work the same exact way also.

The reason I used Only here was because I wanted to ignore selection in Selector Anual field. Set analysis only works within a function. So I can use Only({Set Analysis} ABC), but I cannot do {Set Analysis} ABC. There is another level of complexity here which required us to use Aggr() function. If that was not required, we might have not needed to use Only. Long story short, Only is used to ignore selection in Selector Anual field for the above part of the expression

Only(Aggr(If(RowNo() = NoOfRows(),

count(DISTINCT {<[Tipo Fecha]={$(vTipoFecha)}, Foto={'Publicación anual'}, [Revisión plan anual] = {'Rev0'}>} SOLI_ID_SOLICITUD) +

count(DISTINCT {<[Tipo Fecha]={$(vTipoFecha)}, Foto={'Publicación anual'}, [Revisión plan anual] = {'*'} - {'Rev0'}, ESSO_ID_ESTADO_BLOQ={1}>} SOLI_ID_SOLICITUD)), Mes, Sort))

There is no set analysis used here and hence you can get rid of only function. So, this should work the same way

Aggr(If(RowNo() = NoOfRows(),

count(DISTINCT {<[Tipo Fecha]={$(vTipoFecha)}, Foto={'Publicación anual'}, [Revisión plan anual] = {'Rev0'}>} SOLI_ID_SOLICITUD) +

count(DISTINCT {<[Tipo Fecha]={$(vTipoFecha)}, Foto={'Publicación anual'}, [Revisión plan anual] = {'*'} - {'Rev0'}, ESSO_ID_ESTADO_BLOQ={1}>} SOLI_ID_SOLICITUD)), Mes, Sort)

But I usually like to leave that in, just in case I need to add some sort of set analysis. People tend to forget to add it to outer aggregation functions when it is not there. As soon as I seen Only, I know that I might have to add it to the outer aggregation (Only function in our case) also.

I don't know how helpful this was.... but I am not sure if I can do a better job of explaining this. I really hope that this helps.

Best,

Sunny

Re: Static expressions

mromrod22 — Thu, 02 Feb 2017 07:57:32 GMT

Ok Sunny, now I understand. You do a perfect job explaining me.

Very thanks.