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.

[Anonymous]

Hi Vineeth,

There are so many fields besides date that have multiple entries against the dimension.

firstsortedvalue(LOS, ID-dimension) is resulting to null. "-"

Even =sum(aggr(sum(DISTINCT [ID]), [LOS])) is resulting to 0. I am trying to get the denominator to result to 346 as total LOS seen in my first post.

Thank you.

[Anonymous]

Our database table has ID and LOS dates fields in one table. However, I have to left join about 10 more tables and other tables will have multiple entries for the same ID. I group by or pick the first entry whenever there are multiple entries for a specific ID from the other tables so values on my straightbox are never duplicating.

Thank you.

[vinieme12]

Keep ID and LOS separately instead of doing a left join, will be easier

[Anonymous]

No. LOS comes from the same table as ID. LOS is a difference in days between checkindate and checkoutdate.

Main table with distinct ID has ID, checkindate, and checkoutdate fields in the table. Nothing duplicates in this main table. But, I have to do left join other tables and that is what causes the duplication on the backside (not in the straightbox since I group by or pick the first value). Hope that make sense.

Thanks.

[vinieme12]

Our database table has ID and LOS dates fields in one table

After you are done with consolidating your table do you have ID and LOS  in one table??

if yes then  create a distinct table for calculation purpose

LOAD

     DISTINCT ID,

     MAX(LOS) as #LOS

RESIDENT YOURMAINTABLE;

and then use #LOS for calculation purpose

Hope i've clarified well

[Anonymous · Accepted solution]

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.