SUM TOTAL not working right

[Anonymous]

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.

[vinieme12]

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

[Anonymous]

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.

[Anonymous]

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.

[vinieme12]

It's all in the script

Best of luck

[Anonymous]

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.

[vinieme12]

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

[Anonymous]

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

[Anonymous]

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.

[vinieme12]

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.

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)

[vinieme12]

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