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: 
Bobi
Partner - Contributor II
Partner - Contributor II

% of total (set expression)

Hi. I Want to create a set-expression which calculates the availability for current year.

The rental appartments could have different status: "available", "not available", "temp.blocked". I want to count nr of available in % to the total. 

Table looks like:

 

Available date:        Item nr                         Status

2019-01-01                         24248                     Avaiblable

2019-01-01                         23234                    Not available

 

I have tried with : Count({$<$(vL.CY)>}[Status]='AVA')/Count(TOTAL {$<$(vL.CY)>}[Status])

But not giving right numbers..

 

variable vL.CY is like this:

 [Availability Date.autoCalendar.Year]={"$(=Date(Today(), 'YYYY'))"}

Labels (2)
5 Replies
Gysbert_Wassenaar

Perhaps: Count({$<$(vL.CY),[Status]='AVA'>} [Status])/Count(TOTAL {$<$(vL.CY)>}[Status])

talk is cheap, supply exceeds demand
sunny_talwar

Try this

Count({$<$(vL.CY, Status = {'AVA'}>}[Status])/Count({$<$(vL.CY)>}[Status])

Assuming the value is AVA, but if it Available, try this

Count({$<$(vL.CY, Status = {'Available'}>}[Status])/Count({$<$(vL.CY)>}[Status])
Bobi
Partner - Contributor II
Partner - Contributor II
Author

Hi. Gives error in syntax...

Bobi
Partner - Contributor II
Partner - Contributor II
Author

Works with the syntax but giving the wrong result. (shows 0). Should be more than 20 %.

sunny_talwar

Sorry, my bad, try this

Count({$<$(vL.CY), Status = {'AVA'}>}[Status])/Count({$<$(vL.CY)>}[Status])

or this

Count({$<$(vL.CY), Status = {'Available'}>}[Status])/Count({$<$(vL.CY)>}[Status])