topic Re: problem with summing the rows in QlikView

problem with summing the rows

Thu, 26 Sep 2013 17:01:09 GMT

my scenario i am having

salesman and manager level charts

in the both charts i am using the same expressions but in the salesman chart

the fields are like area,area num, salesman and in manager level chart 1

area, region, manager chart 2

here actually when i sue expression total in the total field the totals are wrong then i changed it to sum of rows then the total are true

but in the the chart 2 there is only one row it is giving me the result as per the expression

isthere any other way to write the expression

RangeSum((sum({<Week={12}>}Vol)/2),sum({<Week={12}>}DISTINCT Units)/2)

Re: problem with summing the rows

Thu, 26 Sep 2013 17:11:18 GMT

is there any way to write this expression with out using distinct function

RangeSum((sum({<Week={12}>}Vol)/2),sum({<Week={12}>}DISTINCT Units)/2)

Re: problem with summing the rows

Thu, 26 Sep 2013 18:20:12 GMT

can anyone help me on this

thanks in advance

Re: problem with summing the rows

Clever_Anjos — Thu, 26 Sep 2013 18:51:12 GMT

Could you post a sample app?

I´ll write the expression for you

Re: problem with summing the rows

v_iyyappan — Thu, 26 Sep 2013 19:09:08 GMT

Hi,

Dont use sum with distinct function. its not meaningful. If i understand correct means use like this

= (Sum({<Week={12}>}Vol)/2) + (Count({<Week={12}>}DISTINCT Units)/2)

Regards,

Re: problem with summing the rows

Thu, 26 Sep 2013 19:11:20 GMT

may i know where can i attach the file

Re: problem with summing the rows

Thu, 26 Sep 2013 19:15:20 GMT

thanks for the response but it is not working

Re: problem with summing the rows

Clever_Anjos — Thu, 26 Sep 2013 19:21:47 GMT

Which expression? The last one?

Re: Re: problem with summing the rows

Thu, 26 Sep 2013 19:46:16 GMT

if you see the totals for 9/13/2013 in chart one CB Data is 120 since i am using distinct

and in the second chart CB data value is 210 since i apply distinct but there i am using sum of rows

Re: problem with summing the rows

Clever_Anjos — Thu, 26 Sep 2013 20:07:47 GMT

Find the "problem":

The expression shows different values because "Mary Herrera" and "Rebecca O'Marrah" have same CB_UNITS, so when your graph splits by RepName, those are counted separated and on other graph they are aggregated together.

Without knowing more about your data and business rules,I can´t help you more than this:

sum(aggr(sum({<WeekDate={41530}>}distinct CB_Units)/DailyDoseConvFactor,RepName))

in first graph will fix your values, please check if is what you need

Re: problem with summing the rows

Thu, 26 Sep 2013 20:38:22 GMT

thanq very much now it is calculating according to the repname and i am getting correct values

Re: problem with summing the rows

Fri, 27 Sep 2013 19:16:46 GMT

hi anjos

i am able to fix one issue but when i try to use the same formula for my line graph i am unable to get the correct values RangeSum((sum(VolUnits)/2) , sum(aggr(sum(DISTINCT CB_Units)/DailyDoseConvFactor,RepName)))

and the graph dimension is weekdate

can you please help me on this

Re: problem with summing the rows

Clever_Anjos — Fri, 27 Sep 2013 19:52:56 GMT

Try

sum(aggr(sum({<WeekDate={41530}>}distinct CB_Units)/DailyDoseConvFactor,WeekDate,RepName)) or

sum(aggr(sum({<WeekDate={41530}>}distinct CB_Units)/DailyDoseConvFactor,RepName,WeekDate))

Re: problem with summing the rows

Fri, 27 Sep 2013 23:05:26 GMT

thanq very much it works perfectly