topic set analysis with sum not work in QlikView

set analysis with sum not work

zturaiki — Wed, 29 May 2013 06:12:57 GMT

Hello

I have this table with values ,

Patient Visit #	Gross Amount	Discount Amount	Charge year	Charge Month	Hospital
5	90	30	2012	2	AMC

I craeted straight table with set analysis to calculate the Net amount based on the charge date and charge year and the hospital , as i have many hospital

i am trying to show the revenue

this is my set analysis :

sum({<Hospital={'AMC'},Charge_Year={2012'},Charge_Month={'02'}>}(Gross_Amount-Discount_Amount))

but it gave me wrong result = 126

the correct result is 70

Re: set analysis with sum not work

Wed, 29 May 2013 06:18:15 GMT

Hi,

Can you please attach the sample file.

Regards

Yusuf

Re: set analysis with sum not work

er_mohit — Wed, 29 May 2013 06:39:59 GMT

try this

sum(distinct {<Charge_Year={2012},Hospital={'AMC'},

Charge_Month={2}>}Gross_Amount - Discount_Amount)

Re: set analysis with sum not work

Wed, 29 May 2013 06:41:22 GMT

try this

if(

Hospital='AMC' and

Charge_Year=2012 and
Charge_Month=2 ,

(Gross_Amount-Discount_Amount)

)

sum({<Hospital={'AMC'},Charge_Year={'2012'},Charge_Month={'2'}>}(Gross_Amount-Discount_Amount))

Re: set analysis with sum not work

kumarnatarajan — Wed, 29 May 2013 06:42:56 GMT

Hi,

Field name is case sensitive. First check your field name.

EX: Charge year = [Charge year]

Don't use Charge_year

Ex; Charge_Year = Charge_Year

This is right one

try below syntex.

sum({<Hospital={"AMC"},Charge_Year={"2012"},Charge_Month={"2"}>}Gross_Amount-Discount_Amount)

Re: set analysis with sum not work

zturaiki — Wed, 29 May 2013 08:49:49 GMT

Attached Zip folder - QVD table + Qlikview doctument

even if i use simple calculation sum(Gross amount - discount amount) , it will give me wrong result ...

Re: set analysis with sum not work

Wed, 29 May 2013 09:06:13 GMT

HI ,

The value showing in the Chart is Correct .

I think you are verifying the values using Table Box .

But Table Box wont show the Duplicate records as 2 rows it shows as only 1 row.

In your Qvd the Rows are Duplicated so thats why you are getting different value .

There are 2 rows of same value .

Please verify .

Regards

Yusuf

Re: set analysis with sum not work

zturaiki — Wed, 29 May 2013 09:26:32 GMT

Yes ,, there is dublicated value ,, You are correct ..

Re: set analysis with sum not work

zturaiki — Wed, 29 May 2013 09:27:44 GMT

Yes ,, there is dublicated value ,, You are correct ..