set analysis with sum not work

[zturaiki]

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

[#2]

Hi,

    Can you please attach the sample file.

Regards

Yusuf

[er_mohit]

try this

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

Charge_Month={2}>}Gross_Amount - Discount_Amount)

[#4]

try this

if(

Hospital='AMC' and 

Charge_Year=2012 and
Charge_Month=2  ,

(Gross_Amount-Discount_Amount)

)

or

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

[kumarnatarajan]

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)

[zturaiki]

Attached Zip folder - QVD table + Qlikview doctument

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

[#7 · Accepted solution]

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

[zturaiki]

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

[zturaiki]

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