Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

sum function not working correctly

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

7 Replies
jedgson
Contributor

sum function not working correctly

Alex,

Could you upload an example of your data?

Jay

Not applicable

sum function not working correctly

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

jedgson
Contributor

sum function not working correctly

You can scramble the data in Document Properties.

Not applicable

sum function not working correctly

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

Partner
Partner

sum function not working correctly

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

Not applicable

sum function not working correctly

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

Not applicable

sum function not working correctly

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.