topic Re: Limit rows in pivot chart in QlikView

Limit rows in pivot chart

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

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

Re: Limit rows in pivot chart

ali_hijazi — Tue, 30 Jan 2018 14:32:53 GMT

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)

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

hope this helps

Re: Limit rows in pivot chart

qv_bazz_10 — Tue, 30 Jan 2018 14:42:49 GMT

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

Re: Limit rows in pivot chart

Anil_Babu_Samineni — Tue, 30 Jan 2018 14:46:24 GMT

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)

Re: Limit rows in pivot chart

qv_bazz_10 — Tue, 30 Jan 2018 14:55:24 GMT

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

Re: Limit rows in pivot chart

Anil_Babu_Samineni — Tue, 30 Jan 2018 15:16:24 GMT

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

Re: Limit rows in pivot chart

qv_bazz_10 — Tue, 30 Jan 2018 16:03:23 GMT

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

Re: Limit rows in pivot chart

Anil_Babu_Samineni — Wed, 31 Jan 2018 00:17:15 GMT

Yes, We can using Rank() function.

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

Re: Limit rows in pivot chart

qv_bazz_10 — Wed, 31 Jan 2018 09:03:10 GMT

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

Re: Limit rows in pivot chart

Anil_Babu_Samineni — Wed, 31 Jan 2018 12:56:18 GMT

Better, If you share sample application

Re: Limit rows in pivot chart

qv_bazz_10 — Wed, 31 Jan 2018 14:03:37 GMT

I hope i could but, as i said before, unfortunately i can't because i'm working on a shared .qvw file. Thanks anyway Anil.