topic Re: Sum of Amount in QlikView

Sum of Amount

upaliwije — Tue, 16 Sep 2014 17:53:07 GMT

I have following data in my pivot table

IDNo Days_left Total Days Amount dept calculation

1 10 365 1000 x amount*days_left/TotaL_days =27

2 30 365 2500 x

3 40 365 4000 y

4 100 365 10000 y

5 120 365 5000 y

When I cal calculate the above it works fine against individual rows but when I group by Dept Amount shown under calculation is zero

what is the expression I should write please

vijay_iitkgp — Tue, 16 Sep 2014 17:55:13 GMT

Hi , You can use Sum(Amount*DaysLeft/Total_days). It will give you correct result.

JonnyPoole — Tue, 16 Sep 2014 17:55:30 GMT

i would start by putting in some aggregation functions so that it knows how to aggregate the numbers:

like this: sum( amount * days) / sum(Total Days)

See if that works first.

maxgro — Tue, 16 Sep 2014 18:01:02 GMT

maybe

sum(Amount*Days_left)/sum([Total Days] )

upaliwije — Tue, 16 Sep 2014 18:11:23 GMT

Thanks all

But none of the expression works even for a single row

but my following expression work for individual rows

=(AMOUNT)*(DAYS_LEFT/TOTAL_DAYS)

JonnyPoole — Tue, 16 Sep 2014 18:19:42 GMT

how about this ? i just changed the exact syntax to match the field names in your example above

sum([AMOUNT]*[DAYS_LEFT]) / sum ([TOTAL_DAYS])

upaliwije — Tue, 16 Sep 2014 18:24:59 GMT

that is also not working even against individual rows

JonnyPoole — Tue, 16 Sep 2014 19:50:57 GMT

Can you share the QVW ? I'm sure its a small tweak to the expression but i don't want to guess..