Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
would you be able to post qvw. by that time check if you have suppressed null in dimension, just uncheck that.
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.
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.
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.
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.
I understand.
the basic problem is in your case sum of rows and expression total are not equal, right
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.
if you compare expressions SUM(LOS) with Just (LOS), you would notice that some of your dimension values are holding multiple LOS values