Static expressions

[mromrod22]

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.

[mromrod22]

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.

[sunny_talwar]

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?

[mromrod22]

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)

[sunny_talwar · Accepted solution]

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

[mromrod22]

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

[sunny_talwar]

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

[mromrod22]

Ok, very thanks for your time.

[mromrod22]

Remember explain me, please!

[sunny_talwar]

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

1)

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

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, 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

[mromrod22]

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

Very thanks.