topic Re: Calculating column percentage in QlikView

Calculating column percentage

Mon, 03 Dec 2012 10:26:53 GMT

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

Re: Calculating column percentage

Mon, 03 Dec 2012 10:38:21 GMT

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?

Re: Calculating column percentage

Mon, 03 Dec 2012 10:50:35 GMT

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!

Re: Calculating column percentage

Gysbert_Wassenaar — Mon, 03 Dec 2012 11:36:31 GMT

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

Re: Calculating column percentage

Mon, 03 Dec 2012 12:43:38 GMT

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.

Re: Calculating column percentage

Mon, 03 Dec 2012 13:05:01 GMT

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!

Re: Calculating column percentage

Mon, 03 Dec 2012 14:04:38 GMT

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

Re: Calculating column percentage

Gysbert_Wassenaar — Mon, 03 Dec 2012 15:24:49 GMT

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.

Re: Calculating column percentage

Sat, 22 Mar 2014 00:34:28 GMT

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!