Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.
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
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