# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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!..

Tags (2)
1 Solution

Accepted Solutions
Not applicable

## Re: How to get distinct sum based on date.

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.

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

18 Replies
MVP

## Re: How to get distinct sum based on date.

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:

Date as TimeStamp,

Date(Floor(Date)) as Date,

Amount

FROM...

and then may be this

Dimension:

Order No.

Date

Expression

Sum(Distinct Amount)

Esteemed Contributor

## Re: How to get distinct sum based on date.

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?

MVP

## Re: How to get distinct sum based on date.

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

## Re: How to get distinct sum based on date.

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.

Esteemed Contributor

## Re: How to get distinct sum based on date.

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

ABC:

OrderNo,

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

Amount,

Net_amt

From YourTable;

NoConcatenate

*

resident ABC

group by Date;

Drop table ABC;

MVP

## Re: How to get distinct sum based on date.

I still think that Floor is what you need to use

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

## Re: How to get distinct sum based on date.

Balraj, My table structure looks like

Order_Date,

Amount,

Net_amount from

Table 1

Select

Order_Date,

Amount,

Net_amount from

Table 1;

Join(Table1)

Select * from Table 2

Join (Table 1)

Select * from Table 3

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)

Select * from Table 5

Join (Table 4)

Select * from Table 6

Concatenate(Table1)

Resident Table4;

DROP Table4;

In this case how to implement the above logic.

Not applicable

## Re: How to get distinct sum based on date.

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

Valued Contributor

## Re: How to get distinct sum based on date.

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:

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:

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

RESIDENT 100;

DROP TABLE 100;

Map_total:

sum(Net_amt)

RESIDENT 200

NoConcatenate

300: