topic Re: wrong sum in QlikView

wrong sum

soniasweety — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

tresesco

felipedl‌

dx.anupam‌

simone.spanio

i am using pivot table

Dimension :

Product

Measure: 1 :

service :

=If(sum({<SERVICE_TYPE={'Service'}>}Amount)/1000000000 > 1,

Num(round(sum({<SERVICE_TYPE={'Service'}>}Amount)/1000000000),'$ ####B'),

If(sum({<SERVICE_TYPE={'Service'}>}Amount)/1000000>1,

Num(round(sum({<SERVICE_TYPE={'Service'}>}Amount)/1000000),'$ ####M'),

Num(round(sum({<SERVICE_TYPE={'Service'}>}Amount)/1000),'$ ####K')))

Measure2:

HW:

=If(sum({<SERVICE_TYPE={'Hardware'}>}Amount)/1000000000 > 1,

Num(round(sum({<SERVICE_TYPE={'Hardware'}>}Amount)/1000000000),'$ ####B'),

If(sum({<SERVICE_TYPE={'Hardware'}>}Amount)/1000000>1,

Num(round(sum({<SERVICE_TYPE={'Hardware'}>}Amount)/1000000),'$ ####M'),

Num(round(sum({<SERVICE_TYPE={'Hardware'}>}Amount)/1000),'$ ####K')))

Measure 3: this is total sum(Measure1)+sum(Measure2) or column1 +column2

=If(sum(USD_Amount)/1000000000 > 1,

Num(round(sum(Amount)/1000000000),'$ ####B'),

If(sum(Amount)/1000000>1,

Num(round(sum(Amount)/1000000),'$ ####M'),

Num(round(sum(Amount)/1000),'$ ####K')))

My issue is Measure3 Total is not giving the correct result you can refer the screen shot. what is the issue? can anyone help me on this?

Thanks

Sony

Re: wrong sum

MarcoWedel — Sat, 08 Sep 2018 17:14:43 GMT

did you try using SERVICE_TYPE as horizontal dimension instead of creating separate expressions each reducing SERVICE_TYPE to a single value with set expressions?

The total then could be calculated with the standard partial sum option.

hope this helps

Marco

Re: wrong sum

soniasweety — Sat, 08 Sep 2018 17:19:34 GMT

i want to show 4 tables to user s --- all four tables giving me wrong totals

1 is By Quarter

2 is by Region

3 Opp_type

4 is By SS

Not by servicetype

Re: wrong sum

soniasweety — Sat, 08 Sep 2018 17:34:35 GMT

if i remove the if condition and that num formating all

its giving me correct sum and correct total.. May be issues with Number formatting how to change to tthe correct expression?

Re: wrong sum

MarcoWedel — Sat, 08 Sep 2018 18:55:34 GMT

please post a sample application to demonstrate

Re: wrong sum

soniasweety — Sun, 09 Sep 2018 06:09:45 GMT

Hi marcowedel

PFA sample

Re: wrong sum

MarcoWedel — Sun, 09 Sep 2018 20:08:01 GMT

How is your expected result different from the results your sample application delivers?

Re: wrong sum

soniasweety — Mon, 10 Sep 2018 05:46:41 GMT

the same number should come that is original data only i exported to excel required columns and prepared the sample.

total should give me the correct

Re: wrong sum

qlikviewwizard — Mon, 10 Sep 2018 06:34:09 GMT

Hi,

Try like this.

SET vScaleNumber=if($1>1000000000, num($1/1000000000,'#,##0.000B'),if($1>1000000, num($1/1000000,'#,##0.000M'),if($1>1000, num($1/1000,'#,##0.000K'),num($1,'#,##0'))));
SET ServiceAndHardware =sum({<SERVICE_TYPE={'Service','Hardware'}>}Total_Opportunity_Value_USD_Amount);

Expression 1: Service

$(vScaleNumber(sum({<SERVICE_TYPE={'Service'}>}Total_Opportunity_Value_USD_Amount)))

Expression 2: Hardware

$(vScaleNumber(sum({<SERVICE_TYPE={'Hardware'}>}Total_Opportunity_Value_USD_Amount)))

Expression 3 : Total

if($(ServiceAndHardware)>1000000000, num($(ServiceAndHardware)/1000000000,'#,##0.000B'),if($(ServiceAndHardware)>1000000, num($(ServiceAndHardware)/1000000,'#,##0.000M'),if($(ServiceAndHardware)>1000, num($(ServiceAndHardware)/1000,'#,##0.000K'),num($(ServiceAndHardware),'#,##0'))))

Re: wrong sum

soniasweety — Mon, 10 Sep 2018 13:49:08 GMT

thanks arjun... Not yet tested. i will test and update you

Re: wrong sum

qlikviewwizard — Mon, 17 Sep 2018 02:27:29 GMT

Hi sony sing

Did you test?