Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
star_limit
Creator II
Creator II

SUM TOTAL not working right

I need to get % of LOS. Second row is just the total. LOS% needs to be individual LOS divide by total LOS.

Currently, I am using LOS/SUM(TOTAL LOS)   as LOS% in a chart straight box. As you see 3/346 needs to be 0.0087 but my application is getting 0.0055. Then LOS% should add up to 100%.

When I try to verify in a listbox using =SUM(Total LOS) , it results to 546. It needs to be 346.

Has anyone run into this issue and know of a possible solution? So, it is dividing 3 by 546 instead of 346.

The weird this is this logic works on a sample application, but the same logic is not working right on my main work dashboard.

Thank you.

1 Solution

Accepted Solutions
star_limit
Creator II
Creator II
Author

I got help from a QlikView expert and here is the solution:

LOS/SUM(TOTAL AGGR(AVG(if(IsNull(LOS),0,LOS)), ID))


Have a great day!

Thanks for your time.

View solution in original post

25 Replies
qlikrajan
Creator III
Creator III

would you be able to post qvw. by that time check if you have suppressed null in dimension, just uncheck that.

vinieme12
Champion III
Champion III

1)Is LOS expression just  =SUM(LOS) or is there any condition to it? i  mean set analysis if yes, apply the same to the SUM(TOTAL LOS) expression.

2)Do you have null values in your dimension with LOS values? this could be another reason.

3) Do you have a calculated dimension?

Post a sample app if you could.

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
star_limit
Creator II
Creator II
Author

I checked and confirmed that I am not suppressing the null value in dimension. LOS is just a value field that I have coded in my sql script. DATEDIFF(day,Coalesce(a.CheckinDateTime,a.ServiceDateTime),a.CheckoutDateTime) AS LOS. And, here is how I am using as an expression: if(IsNull(LOS),0,LOS). So, LOS field is getting the right values but LOS% not doing the right percentage.

I have 77 fields on my application so just trying to give an overall idea to see if any thoughts can solve.

qlikrajan
Creator III
Creator III

Star, now it's difficult to predict from here without looking at the qvw. I dont know how difficult it's for you to post a sample.

star_limit
Creator II
Creator II
Author

qlikrajan! With sample file, the logic is working but since I have 77 fields on my original app and because of privacy issue, I am trying to avoid uploading any more details. I am trying the exact logic on my work app, but it's not functioning right.

qlikrajan
Creator III
Creator III

I understand.

qlikrajan
Creator III
Creator III

the basic problem is in your case sum of rows and expression total are not equal, right

vinieme12
Champion III
Champion III

I think this is what is happening with your data.

check the Attached App and your data for Duplicates or Null values in your Dimension field.

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

if you compare expressions SUM(LOS)  with Just (LOS), you would notice that some of your dimension values are holding multiple LOS values

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.