Calculating column percentage

[#1]

Hi,

I just started using the Microsoft Internet Explorer-based Version of QlikView (Version 11) and need your help with a calculation in a table.

I have defined a table with the columns "Month", "Business Unit", "Count" and "%" as follows:

The column "Month" shows the last three month of the report, the "Count" shows all customer-calls of a business unit. The column "%" needs to show the results of the calculation "Count" per "Business Unit" per "Month" divided by the total "Count" of all "Buiness Units" per "Month" (e.g.: 6/1947 = 0,31%).

As I could not figure out the correct code to realize this calculation, I have implemented a static caluclation that works for one month only:

=Sum(

{

<ticket_year_month = {"$(='>='&date(addmonths(date(monthStart(Today())), -3), 'YYYY-MM')&'<'&date(monthStart(Today()), 'YYYY-MM'))"}>

}

Anzahl)/1947

As I need a solution for this calculation to be dynamic (as described above), I would like to ask you kindly to help me with fixing the formula.

I thank you very much for your support and am looking forward to hearing from you!

Regards,

Max

[#2]

Hi Max,

Do you already have a field to indicate which months should be considered as the latest 3?

If so, you can set your dimensions as:

1) If ( Month_number <4, Year-Month )

2) Business_Unit

Then your formula simply needs to be:

Count(Anzahl) / Count({< [Business Unit] = {A,B,C,D} >} Anzahl)

Does that work?

[#3]

Hi,

no I haven't yet defined a field for the last three months. Do you know how to define fields in the view of the MS IE-plugin?

Thank you very much for your help!

[Gysbert_Wassenaar]

try: count(Anzahl)/count(total <Month> Anzahl)

[#5]

Hi,

There is a tick box "relative" that you can use.  You use the same expression as your count field, and just tick this box.

[#6]

Hi,

thanks to your advice, I implemented the following code:

=Count(
     {
     <ticket_year_month = {"$(='>='&date(addmonths(date(monthStart(Today())),
-1), 'YYYY-MM')&'<'&date(monthStart(Today()), 'YYYY-MM'))"}>
     }
     Anzahl) /
     Count(total
     {
     <ticket_year_month = {"$(='>='&date(addmonths(date(monthStart(Today())),
-1), 'YYYY-MM')&'<'&date(monthStart(Today()), 'YYYY-MM'))"}>
     }   
     Anzahl
     )

This solutions provides the correct calculations for the last month (customer calls per business unit per month divided by total customer calls per month).

However, now I need an expansion of this formular to include the last three month. I cannot just type "-3" in the formular as this will result in an overall calculation of the percentage of three months.

Is this something I can solve by setting a condition on my dimension "Month"?

Or do you have any other ideas?

I thank you very much for your help!

[#7]

Unfortunately, there is not tickbox "relative"...

[Gysbert_Wassenaar]

Your set analysis expression is evaluated only once for the entire chart, NOT per row. So you can't use it to calculate per month percentages for more than one month at a time. Use count(Anzahl)/count(total <Month> Anzahl). If you want to show only the last three months, use a calculated dimension or dimension limits or select the months etc.

[#9]

good nifght!

you can use count('your_information')/Aggr(NODISTINCT Sum(TOTAL <Month> 'your_information'), Month)

if dont work, you can do a nem column (month) by load, that work!