Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

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
Author

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)


talk is cheap, supply exceeds demand
Not applicable
Author

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
Author

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
Author

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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!