Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Calculating column percentage

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:

Tabe percentage.JPG

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

8 Replies
Not applicable

Re: Calculating column percentage

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?

Not applicable

Re: Calculating column percentage

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

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


talk is cheap, supply exceeds demand
Not applicable

Re: Calculating column percentage

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.

Not applicable

Re: Calculating column percentage

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!

Not applicable

Re: Calculating column percentage

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

Re: Calculating column percentage

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.


talk is cheap, supply exceeds demand
Not applicable

Re: Calculating column percentage

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!

Community Browser