Limit rows in pivot chart

[qv_bazz_10]

Hi guys,

i'm trying to limit the number of rows displayed in the following pivot chart:

As you can see, i have a pair totals for expressions (and a calculated %) and i'd like to limit the rows for those with total of "#Mancante" expression higher than 0. Here is the definition of the "#Mancante" expression:

count(if(STATO_MISURA_ATT_MENS_FULL='Mancante',0))

Can anybody help me? I've been trying different solutions from other topics but without success.

N.B. I can't edit the script because i'm working on a shared document.

Thanks in advance,

Andrea

[ali_hijazi]

if you don't want to calculate the count when STATO_MISURA_ATT_MENS_FULL is Mancante

then you can write the following:

count({<STATO_MISURA_ATT_MENS_FULL-={"Mancante'">}measure)

or

count({<STATO_MISURA_ATT_MENS_FULL={"*"}-{"Mancante'">}measure)

hope this helps

[qv_bazz_10]

Hi Ali,

first of all, thanks for your superfast reply.

I don't want to count STATO_MISURA_ATT_MENS_FULL when the sum of "Mancante" is 0. I'm trying to use your expressions but what do i have to write for "measure"?

Thanks again,

Andrea

[Anil_Babu_Samineni]

Usually, You can write something like

count({<STATO_MISURA_ATT_MENS_FULL -= {"=Sum({<STATO_MISURA_ATT_MENS_FULL = {'Mancante'}>} STATO_MISURA_ATT_MENS_FULL) = 0"}>} STATO_MISURA_ATT_MENS_FULL)

[qv_bazz_10]

Hi Anil,

I just tried your expression but it's not working. I don't know if it's important but i have the same expression "#Mancante" in the sort TAB for the "POD" dimension.

Thanks guys,

Andrea

[Anil_Babu_Samineni]

Okay, Then how can i offer you? Will you able to share sample?

[qv_bazz_10]

Unfortunately i'm not able to share anything...if it's hard to achieve what i asked, is there a way to simply limit the number of rows displayed in a pivot to, for example, the top 100 records?

Thanks again Anil,

Andrea

[Anil_Babu_Samineni]

Yes, We can using Rank() function.

Aggr(If(Rank(Sum(Measure))<=100, STATO_MISURA_ATT_MENS_FULL), STATO_MISURA_ATT_MENS_FULL)

[qv_bazz_10]

Sorry Anil, do i have to put the code in the expression or in a calculated dimension? And what do i have to write for "measure"?

I don't know if it can be useful but the expression "#Mancante" count how many times in a year the field "STATO_MISURA_ATT_MENS_FULL" has value "Mancante", so the count can go from 0 to 12.

Thanks again,

Andrea

[Anil_Babu_Samineni]

Better, If you share sample application