topic Re: Month Sorting and Background Color in Pivot Table in QlikView

Month Sorting and Background Color in Pivot Table

MK_QSL — Tue, 05 Nov 2013 13:45:48 GMT

Addition to my previous question….

1) How can I sort Months as Jan,Feb,Mar,Apr etc…
2) How to get Months number from Months name......
3) Need Green Background of cell if sales is higher compare to previous month….and red or orange if lower….

i.e. (this might be wrong as Months are not sorted but below example is for reference only.

Branch Code	TOP 3	Month	Total	#######	#######	#######	#######	#######	#######
Branch 01	pshan		105,706	15,139	15,876	27,376	18,801	17,567	10,948

jonathandienst — Tue, 05 Nov 2013 14:00:17 GMT

To convert a MonthName (text field) into a month dual value:

Month(Date#(MonthName & ' 2013', 'MMM YYYY')) --- if MonthName = 'Jan', 'Feb', ....

Month(Date#(MonthName & ' 2013', 'MMMM YYYY')) --- if MonthName = 'January', 'February', ....

Now sort the months by numeric value as they are duals (which has a number underlying value).

For the comparison, use Before() or Before(TOTAL) function.

HTH

Jonathan

mdmukramali — Tue, 05 Nov 2013 14:05:48 GMT

Dear,

Kindly find the attachment.

i hope it will help you.

israrkhan — Tue, 05 Nov 2013 14:10:59 GMT

for background Try This expression

=if( Before(sum(Sales))<=sum(Sales),Green(),Red()),

or you can Use RGB Value instead of green() and red().

Like RGB(200,100,0), RGB = RED>> GREEN>> BLUE

for sorting, Check Text A>Z and uncheck load order..