topic Re: SUM TOTAL not working right in QlikView

SUM TOTAL not working right

Anonymous — Tue, 26 Jul 2016 19:07:14 GMT

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.

Re: SUM TOTAL not working right

Anonymous — Tue, 26 Jul 2016 19:12:36 GMT

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

Re: SUM TOTAL not working right

vinieme12 — Tue, 26 Jul 2016 19:18:45 GMT

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.

Re: SUM TOTAL not working right

Anonymous — Tue, 26 Jul 2016 19:27:29 GMT

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.

Re: SUM TOTAL not working right

Anonymous — Tue, 26 Jul 2016 19:30:51 GMT

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.

Re: SUM TOTAL not working right

Anonymous — Tue, 26 Jul 2016 19:45:54 GMT

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.

Re: SUM TOTAL not working right

Anonymous — Tue, 26 Jul 2016 19:46:47 GMT

I understand.

Re: SUM TOTAL not working right

Anonymous — Tue, 26 Jul 2016 19:51:53 GMT

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

Re: SUM TOTAL not working right

vinieme12 — Tue, 26 Jul 2016 19:54:08 GMT

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.

Re: SUM TOTAL not working right

vinieme12 — Tue, 26 Jul 2016 19:59:03 GMT

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

Re: SUM TOTAL not working right

vinieme12 — Tue, 26 Jul 2016 19:59:28 GMT

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

Re: SUM TOTAL not working right

Anonymous — Tue, 26 Jul 2016 20:01:57 GMT

Expression total evaluates the expression over the whole data set which may or may not equal the sum of the rows. if some of the records from source data is not coming in your straight table. That's what I can think of now.

Re: SUM TOTAL not working right

Anonymous — Tue, 26 Jul 2016 20:04:19 GMT

Vineeth,

Looks like it is exactly happening what you mentioned and did in the sample app. However, my dimension is a unique field and when I export my data into excel file, I see the dimension is never duplicating. May be an expression might be duplicating on the back side of the logic/script. I will give it a deep look tomorrow and update you all.

Thanks for thoughts.

Re: SUM TOTAL not working right

vinieme12 — Tue, 26 Jul 2016 20:07:21 GMT

It's all in the script

Best of luck

Re: SUM TOTAL not working right

Anonymous — Tue, 26 Jul 2016 20:09:12 GMT

may be your original dataset have 10 fields and you are importing just 8 fields. Removal of 2 fields might have caused a number of duplicate records. you can compare the number of rows by doing "ctrl T" in table viewer and number of rows in your table box. If they are not same then definitely there are some duplicates.

Re: SUM TOTAL not working right

vinieme12 — Tue, 26 Jul 2016 20:10:31 GMT

It is much likely a JOIN you are doing to the table that is causing the lines to duplicate

Re: SUM TOTAL not working right

Anonymous — Tue, 26 Jul 2016 20:20:04 GMT

When I add the data to the tablebox, I see some rows duplicating because of an expression having multiple amounts of different dates. However, I am using a straightbox and I do sum(amount) so in a straightbox none of the expression duplicates. Some IDs have multiple insurances so in a tablebox, rows duplicates with different insurances. However, in my straightbox, I pick only the first insurance(primary) so there will no duplicates. To conclude, it seems the straightbox chart doesn't duplicate any values but sum(LOS) adds duplicating values as it is doing in the tablebox. Hope that made sense. Gotta go. ttyl

Re: SUM TOTAL not working right

Anonymous — Wed, 27 Jul 2016 13:02:51 GMT

Hey all,

I was thinking for LOS% field the numerator will be: if(IsNull(LOS),0,LOS) . However, I am not sure how to code the denominator. It looks like it should be "sum of LOS of the currently selected distinct ID". I am trying =Sum({$<DISTINCT ID>}LOS) . But, DISTINCT is getting a red line underneath. How can I write that denominator? Any thoughts?

Thank you.

Re: SUM TOTAL not working right

vinieme12 — Wed, 27 Jul 2016 13:12:06 GMT

Is it just the dates that have multiple entries against your dimension that are causing the duplicate rows? try the below

firstsortedvalue(LOS, ID-dimension)

Re: SUM TOTAL not working right

vinieme12 — Wed, 27 Jul 2016 13:14:14 GMT

a Better way to avoid this to create a separate table to hold distinct values of ID and LOS for calculation purpose