6 Replies Latest reply: Nov 23, 2010 9:54 AM by Matteo Fioroni RSS

    pivote table

      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

          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

            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

                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

                    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