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

Supressing/replacing unwanted calculated %values

Hi all

I'm working at a University, where we just got QV. I'm relatively new to the program and have run in to a problem when calculating precentage.

In a column I want to show "Number of Students" (sum of the variable "Frequency") for each Study, as a percentage of max(year) selected compared to the year before.

I've done it like this:

 

=num ((Sum( {$<Year={$(vYear)}>} Frequency ) - Sum( {$<Year={$(vPrevYear)}>} Frequency ))/
      
Sum( {$<Year={$(vPrevYear)}>} Frequency ),'#.##0%')

It works fine. The only problem I have is, that it caluculates "-100%" when there are zero students in the selected year (because the Study is closed) and at least 1 student the year before. When I sort the data by negativ values, all the closed studies come out on top and I don't want that (they are not that important ).

The solution for would be to replace the -100% by either a "-" or a text string like "No enrollment the selected Year". Can it be done?

I apreciate any help you have

Thanks a lot

Cheers

Bruno

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Try this:

if(Sum( {$<Year={$(vYear)}>} Frequency )=0, 'No enrollment in the selected Year',

num ((Sum( {$<Year={$(vYear)}>} Frequency ) - Sum( {$<Year={$(vPrevYear)}>} Frequency ))/

       Sum( {$<Year={$(vPrevYear)}>} Frequency ),'#.##0%') )


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
Gysbert_Wassenaar

Try this:

if(Sum( {$<Year={$(vYear)}>} Frequency )=0, 'No enrollment in the selected Year',

num ((Sum( {$<Year={$(vYear)}>} Frequency ) - Sum( {$<Year={$(vPrevYear)}>} Frequency ))/

       Sum( {$<Year={$(vPrevYear)}>} Frequency ),'#.##0%') )


talk is cheap, supply exceeds demand
Not applicable
Author

Thanks a lot for the quick reply!

It worked perfectly

Cheers

Bruno

Not applicable
Author

Thanks a lot for the quick reply!

It worked perfectly

Cheers

Bruno