Strange Problem

[ttal7878]

Hi,

I have a strange problem,

When I try to make a sum of the sale_Amount$ column, for no apparent reason the result doubles itself.

It's problematic for me to upload my real model,

and I can't imitate the problem in an example model, So I'll try to explain with picture from my model.

I have a fact table that displays the following data

You can see in the picture that the total Sale Amount$ for the Unique_num is:24$

But, if I do the following calculation:

=sum({<Action=,SaleDateKey={">=$((VMinDate))<=$((VMaxDate))"}>}Sale_Amount$)

I get the result 327$

You can see in the picture that for 'Action' = 'collection', the Sale_Amount$ has doubled.

Can anyone explain, what I'm doing wrong?

[devarasu07]

Hi,

What is the expression inside sum of sales amount $? always try to share your mock source as text/excel instead of pitcure. tks

might date format issue . could you share your mock app? tks

Data:

LOAD *,Timestamp(Timestamp#(SaleDateKey1,'YYYY/MM/DD hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as  SaleDateKey INLINE [

    Unique_Num, Action, Sale_Amount$, SaleDateKey1

    177796860, Collection, 303,2017/10/16 3:17:05

    177796860, Sale, 15,2017/10/15 5:59:43

    177796860, Lead, 0,2017/10/15 5:56:08

    177796860, Refund,-294,2017/10/25 9:02:17

];

[sunny_talwar]

I think this might be a data model issue.... it might be duplicating the rows and because you are looking at this in a table box, it only displays the unique combination of your listed dimensions, you are only seeing the values just once

[effinty2112]

Hi Tal Tal,

It's possible that the record for the collection of 177796860 is duplicated. If you add this expression

count({<Action=,SaleDateKey={">=$((VMinDate))<=$((VMaxDate))"}>}Sale_Amount$)

does it return 1 or 2?

Good luck

Andrew

[ttal7878]

How can I know if the model duplicating the rows?

There is a possibility of testing, in order to know that the problem is in the model?

[ttal7878]

It return  2

[effinty2112]

Hi Tal Tal,

I'm afraid then that the record for the collection of this item must be duplicated in your data model so you'll need to go through your load script to look for the error.

good luck

Andrew

[sunny_talwar]

May be add the other fields you have in this table to this table box and see if you can find something which might be repeating

[ttal7878]

This is a table with 200 fields,

well I'll try......

Thanks

[ttal7878]

Thank you, at least now I know the source of the problem