Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
ttal7878
Creator
Creator

Strange Problem

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$


Fact table.JPG

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.

Capture.JPG

Can anyone explain, what I'm doing wrong?

11 Replies
devarasu07
Master II
Master II

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

];

Capture.JPG

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
Master
Master

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
Creator
Creator
Author

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
Creator
Creator
Author

It return  2

effinty2112
Master
Master

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
Creator
Creator
Author

This is a table with 200 fields,

well I'll try......

Thanks

ttal7878
Creator
Creator
Author

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