Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to get distinct sum based on date.

Hi

There is a requirement where Amount is same for one order number and received on same date but on different timing.

Ex.

Order No.          Date                         Amount          Net_amt

1                    1/1/2000 01:00:00             -10               5

1                    1/1/2000 01:00:01             -10              4

1                     1/1/2000 01:00:03            -10               6

In my requirement I have to consider Amount only one time and Sum(Net_amt).

So I used Sum(Distinct Amount). But because of change in the time all the records are getting displayed in the table and getting summed up to -30.

Afterwards I have gone through some blogs and used this

sum ( aggr( sum(Amount), Order_No))


But even with this the results are displayed 0.


Help me out to overcome the problem!..

1 Solution

Accepted Solutions
Not applicable
Author

Thanks Santosh for your valuable time on explaining the issue.

This logic works on a given example.

In my case there are two parts are there. One for Sales & Another for return where date and Amount comes from. Am concatinating these two tables to make it single table.

So what I did is, I added Trunc(Ordered_Date) column while Loading

and considering Sum(Amount)+Sum(Distinct Return_Amount) in an expression to arrive at the expected result.

View solution in original post

18 Replies
sunny_talwar

Do you have a field which only contains information about date and excludes time? If not, then may be create it in the script like this:

LOAD [Order No.],

          Date as TimeStamp,

          Date(Floor(Date)) as Date,

          Amount

FROM...

and then may be this

Dimension:

Order No.

Date

Expression

Sum(Distinct Amount)

Anonymous
Not applicable
Author

Sunny,

If you are using Date(Floor()) combination then "Distinct" really required?

I think when we use Date(Floor()), it will return single entry of minimum date.

And in above case, it will return 1 record

Order No.          Date                         Amount          Net_amt

1                    1/1/2000             -10               5

correct me if I am wrong?

sunny_talwar

To tell you the truth, I am not 100% sure of what the required output is, but ignoring Net_amt for now, I think the OP does want to see just one row. I can be wrong, but that was my interpretation.

Not applicable
Author

Thanks for your reply Sunny & Balraj.

The desired output should look like

Order No.          Date                         Amount          Net_amt

1                    1/1/2000                       -10                    15

I want Amount to print based on date excluding time. For one order number and date the Amount should print only one time though there are multiple entries but Net_amt should be summed up for all the rows.

Please help me out for this scenario

Anonymous
Not applicable
Author

Then don't use Floor(), try like this?

ABC:

Load

OrderNo,

Date(Date#(Date,'DD/MMM/YYYY')) as Date,

Amount,

Net_amt

From YourTable;

NoConcatenate

Load

*

resident ABC

group by Date;

Drop table ABC;

sunny_talwar

I still think that Floor is what you need to use

LOAD [Order No.],

          Date as TimeStamp,

          Date(Floor(Date)) as Date,

          Amount

FROM...

and then this:

Dimension:

Order No.

Date

Expression

Sum(Distinct Amount)

Sum(Net_Amt)

Not applicable
Author

Balraj, My table structure looks like

Load

Order_Date,

Amount,

Net_amount from

Table 1


Select

Order_Date,

Amount,

Net_amount from

Table 1;


Join(Table1)


Load * from Table 2

Select * from Table 2


Join (Table 1)


Load * from Table 3

Select * from Table 3


Load

Order_Date_RTRN as Order_Date,

Amount_RTRN as Amount,

Net_amount_RTRN as Net_Amount from

Table 4;

Select

Order_Date_RTRN,

Amount_RTRN ,

Net_amount_RTRN from

Table 4;

Join (Table 4)


Load * from Table 5

Select * from Table 5


Join (Table 4)


Load * from Table 6

Select * from Table 6

Concatenate(Table1)

LOAD *

Resident Table4;

DROP Table4;


In this case how to implement the above logic.

Not applicable
Author

I tried this logic, But data is not matching/not proper.

puttemans
Specialist
Specialist

Hello,

Please have a look at the code below. I think it meets your requirements.

It sums the Net_amt per orderno & date, while it also gives you only once the Amount.

100:

LOAD * INLINE [

OrderNo,          Date                  ,       Amount     ,     Net_amt,

1         ,           1/1/2000 01:00:00    ,         -10      ,         5,

1          ,          1/1/2000 01:00:01     ,        -10       ,       4,

1           ,          1/1/2000 01:00:03     ,       -10        ,       6,

2, 1/1/2000 01:02:00, -10 , 5];

NoConcatenate

200:

LOAD *,

  subfield(Date,' ', 1) as Newdate,

  subfield(Date,' ', 1)&'|'&OrderNo as link

RESIDENT 100;

DROP TABLE 100;

Map_total:

MAPPING LOAD

  link,

  sum(Net_amt)

RESIDENT 200

GROUP BY link;

NoConcatenate

300:

LOAD OrderNo,

  Newdate,

  Amount,

  Applymap('Map_total', link, 'none') as total

RESIDENT 200;

DROP TABLE 200;