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: 
philgood34
Creator II
Creator II

CALCULATE TOTAL RATIO WITH SOME DATA = 0

hi

let me introduce my issue

structure.png As you see in these entreprise, there are 2 categories of employees :

COM who produces sales

STAFF who does'nt

Of course the 2 categories consume hours.

I have to calculate the ratio SALES per HOUR  for each sector and total

i use this expression :

If(Sum(SALES) = 0, Sum(TOTAL SALES)/ Sum(HOURS), Sum(SALES)/Sum(HOURS))

(to simplify i took a simple example without needding Aggr & dimensionnality ()=0)


As you see, for staff i need total sales to calculate the ratio:

TOTAL.png

Here comes my issue :

when i have to analyse one COM sector together with one STAFF sector the result is

partiel.png

As you see the ratio is correct for total and COM but not for STAFF

i need the expression to "freeze" the total SALES  independently of sector's selection ...

I don't know the expression for that

hoping some help

regards

Philippe

1 Solution

Accepted Solutions
philgood34
Creator II
Creator II
Author

got it !

If(Sum(SALES) = 0, Sum({1}TOTAL SALES)/ Sum(HOURS), Sum(SALES)/Sum(HOURS))

i hope it will be useful for other new users

regards

Philippe

View solution in original post

1 Reply
philgood34
Creator II
Creator II
Author

got it !

If(Sum(SALES) = 0, Sum({1}TOTAL SALES)/ Sum(HOURS), Sum(SALES)/Sum(HOURS))

i hope it will be useful for other new users

regards

Philippe