topic pivote table in QlikView

pivote table

Tue, 23 Nov 2010 11:08:54 GMT

Hi,

I need some help with my pivot table.

I have two questions.

1. In the two first rows I would like to have year + first selected month + last selected month. Now I have it in numbers but I would like to have the month in words instead. Ex. 2010 from jan up to okt.

Now I have written

=(Max(Year)) & ' from ' & (min(month)) & ' up to ' & (max(month))

Country	Customer	2010 from 1 up to 10	2009 from 1 up to 10	2010 vs 2009 i %	2010 vs 2009 i kr	Totalt 2009	Budget 2010
Armenien	Private enterpr. G.Babakhanyan	0	0	-	0	0	10 000
Armenien	Total	0	0	-	0	0	10 000
Australien	Trollplast Pty Ltd	4 198 711	3 455 199	21,5%	743 512	4 009 019	4 600 000
Australien	Total	4 198 711	3 455 199	21,5%	743 512	4 009 019	4 600 000
Bahrain	Nasser Pharmacy W.L.L.	0	200 673	-100,0%	-200 673	200 673	0
Bahrain	Total	0	200 673	-100,0%	-200 673	200 673	0
Belgien	Cabinet Nawara	8 794	7 771	13,2%	1 023	7 771	475 000
Belgien	Medident	414 275	351 828	17,7%	62 447	387 200	475 000
Belgien	Universitair Ziekenhuis Gent	19 575	77 367	-74,7%	-57 792	77 367	475 000
Belgien	Total	442 644	436 966	1,3%	5 678	472 338	475 000

2. The last row I have budget. The budget is only valid on a country level (not based per customer). I would like to have the budget figures showed on the total not on each customer. Is this possible?

like this:

Country	Customer	2010 from 1 up to 10	2009 from 1 up to 10	2010 vs 2009 i %	2010 vs 2009 i kr	Totalt 2009	Budget 2010
Armenien	Private enterpr. G.Babakhanyan	0	0	-	0	0
Armenien	Total	0	0	-	0	0	10 000
Australien	Trollplast Pty Ltd	4 198 711	3 455 199	21,5%	743 512	4 009 019
Australien	Total	4 198 711	3 455 199	21,5%	743 512	4 009 019	4 600 000
Bahrain	Nasser Pharmacy W.L.L.	0	200 673	-100,0%	-200 673	200 673
Bahrain	Total	0	200 673	-100,0%	-200 673	200 673	0
Belgien	Cabinet Nawara	8 794	7 771	13,2%	1 023	7 771
Belgien	Medident	414 275	351 828	17,7%	62 447	387 200
Belgien	Universitair Ziekenhuis Gent	19 575	77 367	-74,7%	-57 792	77 367
Belgien	Total	442 644	436 966	1,3%	5 678	472 338	475 000

Thanks

Markus

pivote table

Anonymous — Tue, 23 Nov 2010 12:42:25 GMT

For the months name you can use the MonthName or MonthsName that return a string based on the string MonthNames setted up at the script beginning.

For the budget problem you can simply work on the text color property: write in white where you don't want display the values.

pivote table

Tue, 23 Nov 2010 13:02:30 GMT

Thanks for the help with the budget problem. Sometimes it´s very easy

I didn´t follow you with the month. Please describe the script

Markus

pivote table

Anonymous — Tue, 23 Nov 2010 14:05:44 GMT

monthname ( '2001-10-19' ) returns 'Oct 2001' where Oct is your specification for the 10th month in the MontsName variable setted at the script start. (It's setted by default each time you edit a new script and you also edit it).

You can find examples in the help too.

pivote table

Tue, 23 Nov 2010 14:15:54 GMT

Hello,

the min() and similar functions return numbers, even when the input parameter is a date or so. But you want Month to be displayed instead of a number. So format it with the Month()-function. This should be as you expect it:

=(Max(Year)) & ' from ' & Month((min(month))) & ' up to ' & Month((max(month)))

Regards, Roland

pivote table

Tue, 23 Nov 2010 14:30:50 GMT

When I use the Month() function described above the result changed.

This is how it is showed now:

and this is how it's showed of if I change it (insted of 1 up to 10 it changed to dec up to jan)?!?

pivote table

Anonymous — Tue, 23 Nov 2010 14:54:14 GMT

The argument for the month function is a DATE.

DATE tipe is simply a counter (all the days from somthing like 01-01-1900)

if you pass 1 as argment it isn't the 1st month, but the first date qlikview can qualify.

You must use a valid date like:

Month(Max(YEAR)&'/'&Min(MONTH)&'/'&'01') -> depending on your locale setting you must put the year before or after the month...

the 01 is fixed only to build a valid date, we are interested in the month, each date is valid