topic Re: Pivot Table - Top 3 WeekDays in QlikView

Pivot Table - Top 3 WeekDays

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

Hi,

In a Pivot Table I have added Type,Weekday(Sun,Mon,Tue...) as Dimensions and Count(Events) As "Expression".

Now my requirement is have to display Top3 Weekdays as "Green Color" and Remaining weekdays as "Blue color".

Can any one please help me to do.

Thanks.

Re: Pivot Table - Top 3 WeekDays

Kushal_Chawda — Mon, 06 Jun 2016 09:07:13 GMT

Top 3 based on what?

Re: Pivot Table - Top 3 WeekDays

migueldelval — Mon, 06 Jun 2016 09:12:36 GMT

Hi Bhavvi b,

Could you attach qvw file?

Thanks

Re: Pivot Table - Top 3 WeekDays

bhavvibudagam — Mon, 06 Jun 2016 09:20:19 GMT

Based on Count(Events) If we take Grand Total then highest 3 values Related Days has to be displayed in green color

Re: Pivot Table - Top 3 WeekDays

marjan_it — Mon, 06 Jun 2016 09:55:54 GMT

in dimension in background section you can add your script like this:

=if(Aggr(rank(count((Events))),Weekday<=3,RGB(0,255,0),RGB(0,255,255))

Re: Pivot Table - Top 3 WeekDays

qlikview979 — Mon, 06 Jun 2016 09:56:18 GMT

Hi Bhavvi,

Please share your QVW file.

Regards,

Mahesh.

Re: Pivot Table - Top 3 WeekDays

Kushal_Chawda — Mon, 06 Jun 2016 10:01:59 GMT

Go to Dimension Tab of Pivot table-> click + on dimension WeekDays, put below expression background color

=if( Rank(Count( Total <WeekDay>Event),0,1) <=3, green(), blue())

Re: Pivot Table - Top 3 WeekDays

marjan_it — Mon, 06 Jun 2016 10:10:22 GMT

and this is my sample!

Re: Pivot Table - Top 3 WeekDays

bhavvibudagam — Mon, 06 Jun 2016 11:47:34 GMT

Hi Settu, i have tried your expression but it is showing like this. Wed is in Green color.But Mon,Tue and Thur has to displayed in Green Color.

This is the expression what i have used,

=if(aggr(count({<CALL_SOURCE={'ANI/ALI','PHONE','WALK-IN'}>}EVENT_NUMBER),CALL_TYPE)>=Max(TOTAL <CDTS_WeekDay>Aggr(count({<CALL_SOURCE={'ANI/ALI','PHONE','WALK-IN'}>}EVENT_NUMBER),CALL_TYPE),3),

RGB(155,187,89),RGB(149,179,215))

Re: Pivot Table - Top 3 WeekDays

bhavvibudagam — Mon, 06 Jun 2016 11:49:34 GMT

Hi miguel,

It's very large file.Source is in Qvd.I have attached the screen shot below please see it once.

Re: Pivot Table - Top 3 WeekDays

settu_periasamy — Mon, 06 Jun 2016 15:51:46 GMT

It would be good to suggest if you provide the sample.

Re: Pivot Table - Top 3 WeekDays

sunny_talwar — Mon, 06 Jun 2016 16:27:29 GMT

May be this:

=If(Sum(Aggr(Count({<CALL_SOURCE={'ANI/ALI','PHONE','WALK-IN'}>} EVENT_NUMBER), CDTS_WeekDay))>= Max(TOTAL Aggr(Count({<CALL_SOURCE={'ANI/ALI','PHONE','WALK-IN'}>}EVENT_NUMBER), CDTS_WeekDay), 3), RGB(155,187,89),RGB(149,179,215))

Re: Pivot Table - Top 3 WeekDays

bhavvibudagam — Tue, 07 Jun 2016 09:32:03 GMT

=If(SecondaryDimensionality() = 1, If(Count(TOTAL <CDTS_WeekDay>{<CALL_SOURCE={'ANI/ALI','PHONE','WALK-IN'}>} EVENT_NUMBER) >=

Max(TOTAL Aggr(Count({<CALL_SOURCE={'ANI/ALI','PHONE','WALK-IN'}>}EVENT_NUMBER), CDTS_WeekDay), 3),

RGB(155,187,89),RGB(149,179,215)))