topic sum function not working correctly in QlikView

sum function not working correctly

Thu, 14 Jul 2011 14:07:38 GMT

Hello,

I am currently having a problem with the sum function. My revenue for a particular quarter should be for ex: 300M, instead it is around 200B. My expression for this is

sum({<[# of FQs from current FQ]={0,'-1','-4'}>}[TOTAL REVENUE ($)])

I also have graphs summing the total # of billed sales, and they are double or quadruple counting as well, so it must be a problem with my load script? Same formula i just replaced [TOTAL REVENUE ($)] with [TOTAL BILLED SALES]

Furthermore, I have a simple chart that was able to correctly calculate the revenue for each quarter a few days ago, and now is summing just like my charts.

At the same time, from a completely different load file and script I am summing my revenue by product sold, and further my revenue should be summed across 8 quarters with each quarter summing to a different amount, in the form of a pivot table. Instead three of the quarters will have the exact same revenue down to the dollar, then the next 5 will all have the same revenue. My expression is as follows;

sum({$<[# of FQs from current FQ]={0,'-1','-2','-3','-4','-5','-6','-7','-8','-9','-10'}>}[TOTAL REVENUE ($RL)])

I realize that my fields are very similar, but they are different, notice the RL, and the excel file that each one loads from has them correctly labeled.

Thanks so much for all your help,

Alex

sum function not working correctly

jedgson — Thu, 14 Jul 2011 14:15:52 GMT

Alex,

Could you upload an example of your data?

Jay

sum function not working correctly

Thu, 14 Jul 2011 14:32:50 GMT

unfortunetley it contains sensitive data for a publicly traded company so i cannot do that

sum function not working correctly

jedgson — Thu, 14 Jul 2011 16:58:25 GMT

You can scramble the data in Document Properties.

sum function not working correctly

Thu, 14 Jul 2011 18:43:03 GMT

I did this, but what exactly does it do? Should i do it for every field i have loaded or just specific ones?

Thanks,

Alex

sum function not working correctly

erichshiino — Mon, 18 Jul 2011 01:19:49 GMT

Alex,

It will create a kind of random string for the fields you select.

You can do it for all of them, or only for the ones you consider that data more relevant (from the privacy point of view).

After this you could send your application.

You can also make some selections, and use menu File -> Reduce Data -> Keep Possible values.

For both cases, you can restore your data by reloading the application.

An alternative here, would be the creating of a similar data model and similar application where you can reproduce your problem with dummy data. This way the community can take a look and understand your problem.

Hope this helps,

Regards,

Erich

sum function not working correctly

Wed, 20 Jul 2011 14:57:32 GMT

The data is not loading the decimal places correctly. Ex: Revenue that should sum to $1,444,639 in excel is being summed in qlikview as $144,463,924. So the qlikview data is being summed 2 decimal places too much.

Another example is the excel sum is $1,065,221 and then in qlikview its $106,522101.

The number format in qlikview is set to $, and its the same in excel

sum function not working correctly

Tue, 09 Aug 2011 08:30:07 GMT

Good morning, I am trying to sum all the values that match one specific condition but I can sort it out. If first I show all the values I want to sum it is correct:

EventId RISK REVENUE AMOUNT 1

76672 $25,131.50

176674 $25,131.50

176671 $31,600.00

176673 $31,600.00

I used the dimension EventId and this expression:

= IF( (IsNull(NewEventDescription)=0 OR IsNull(NewProjectManager)=0 OR IsNull(NewProjectName)=0 OR IsNull(NewServiceLine)=0 OR IsNull(NewTaskNumber)=0 OR IsNull(NewProjectNumber)=0 OR IsNull(NewEventDate)=0 OR IsNull(NewProjectDescription)=0 OR IsNull(NewTaskName)=0 OR IsNull(NewRevValue)=0 OR IsNull(NewBillValue)=0 OR IsNull(NewCustomerName)=0 OR isnull(DateNewLineAdded)=0 ) AND EventDate>QuarterStart(TodayDate) AND EventDate<QuarterEnd(TodayDate) AND RevenueFlag='N' AND BilledFlag='N' AND ProjectManager='David Williams',

(ProjectedRevenue)

)

but when I use the sum function it prints a result doesnt make any sense cos it doesnt show the total that should be $113.463:

EventId RISK REVENUE AMOUNT

176672 $1,759,205.00

176671 $2,212,000.00

176674 $3,518,410.00

176673 $4,424,000.00

I used the dimension EventId and this expression (the same than before but with the sum function at the begining:

= sum(IF( (IsNull(NewEventDescription)=0 OR IsNull(NewProjectManager)=0 OR IsNull(NewProjectName)=0 OR IsNull(NewServiceLine)=0 OR IsNull(NewTaskNumber)=0 OR IsNull(NewProjectNumber)=0 OR IsNull(NewEventDate)=0 OR IsNull(NewProjectDescription)=0 OR IsNull(NewTaskName)=0 OR IsNull(NewRevValue)=0 OR IsNull(NewBillValue)=0 OR IsNull(NewCustomerName)=0 OR isnull(DateNewLineAdded)=0 ) AND EventDate>QuarterStart(TodayDate) AND EventDate<QuarterEnd(TodayDate) AND RevenueFlag='N' AND BilledFlag='N' AND ProjectManager='David Williams',

(ProjectedRevenue)

)

I didnt selected the option "Full accumulation" in Expressions tab.

Could you please tell me what I am doing wrong?

Thanks a lot in advance.

Re: sum function not working correctly

Vamsi0529 — Thu, 17 Feb 2022 10:14:59 GMT

Hi Everyone,

My requirement I have Duration column like Below.

duration second's

00:00:28

00:00:29

When I Trying to sum i am getting 00:00:57

Can any one help me sum the above seconds