How to get distinct sum based on date.

[#1]

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

[Kushal_Chawda]

try this

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

[#3]

Hi Johan,

Thanks for your reply.

I tried running your script. I need the result in exact fashion.

Since am new to Qliksense am unable to implement the same logic in my script.

If you can implement the same logic to my script it will be of really really helpful to me.

My script is:

LOAD ORG_ID,

    ORG_ID AS ORG_ID1,

    ORGANIZATION_ID,

  HEADER_ID,

  ORDER_NUMBER,

  ORDERED_DATE, // Date column

    ORDER_TYPE,

  CUSTOMER_NAME,

    CUSTOMER_NUMBER,

  SALESREP_ID,

  SALESREP_NAME,

  MOVE_ORDER_STATUS,

  ATTRIBUTE1,

  DISCOUNT_AMT,

    'Sales' as Transaction_type;

[XXOIC_OE_ORDER_HEADERS_ALL]:

SELECT "ORG_ID",

  "ORGANIZATION_ID",

  "HEADER_ID",

  "ORDER_NUMBER",

  "ORDERED_DATE",

  "ORDER_TYPE",

  "CUSTOMER_NAME",

    "CUSTOMER_NUMBER",

  "SALESREP_ID",

  "SALESREP_NAME",

  "MOVE_ORDER_STATUS",

  "ATTRIBUTE1",

  "DISCOUNT_AMT"

FROM "XXOIC"."XXOIC_OE_ORDER_HEADERS_ALL"

where ATTRIBUTE1 is null or ATTRIBUTE1 <> 'Cancel'

and MOVE_ORDER_STATUS is not null;

INNER JOIN(XXOIC_OE_ORDER_HEADERS_ALL)

LOAD ORG_ID AS ORG_ID1,

  ORGANIZATION_ID,

    INVENTORY_ITEM_ID,

    HEADER_ID,

  ORDER_QTY,

    ORDER_NUMBER,

EXTENDED_PRICE,    // Amount

  NET_UNIT_SELLING_PRICE,

  PRICE_AFTER_DISCOUNT ,

  ITEM_COST,

  TRASACTION_ID as TRANSACTION_ID;

[XXOIC_OE_ORDER_LINES_ALL]:

SELECT "ORG_ID",

  "ORGANIZATION_ID",

    "INVENTORY_ITEM_ID",

  "HEADER_ID",

  "ORDER_QTY",

    "ORDER_NUMBER",

  "EXTENDED_PRICE",

  "NET_UNIT_SELLING_PRICE",

  "PRICE_AFTER_DISCOUNT",

  "ITEM_COST",

  "TRASACTION_ID"

FROM "XXOIC"."XXOIC_OE_ORDER_LINES_ALL"

where ITEM_COST <> 0;

INNER JOIN(XXOIC_OE_ORDER_HEADERS_ALL)

LOAD TRANSACTION_ID,

  TRANSACTION_DATE;

[MTL_MATERIAL_TRANSACTIONS]:

SELECT "TRANSACTION_ID",

  "TRANSACTION_DATE"

FROM "INV"."MTL_MATERIAL_TRANSACTIONS"

WHERE TRANSACTION_ID >148100175;

LOAD ORG_ID,

  ORG_ID AS INV_ORG_ID,

  ORGANIZATION_ID,

  HEADER_ID,

  ORDER_NUMBER,

  ORDER_TYPE,

  CUSTOMER_NAME,

  CUSTOMER_NUMBER,

  SALESREP_ID,

  SALESREP_NAME,

  CUST_PO_NUMBER,

  (PRICE_AFTER_DISCOUNT*-1) as EXTENDED_PRICE,  // Amount column concatinating                                                             // //with [XXOIC_OE_ORDER_HEADERS_ALL]

  RETURN_DISC_AMT*-1 as DISCOUNT_AMT,

  TRANS_ID,

    'Return' as Transaction_type;

[XXOIC_LAC_RETURN_HEADERS]:

SELECT "ORG_ID",

  "ORGANIZATION_ID",

  "HEADER_ID",

  "ORDER_NUMBER",

  "ORDER_TYPE",

  "CUSTOMER_NAME",

  "CUSTOMER_NUMBER",

  "SALESREP_ID",

  "SALESREP_NAME",

  "CUST_PO_NUMBER",

  "PRICE_AFTER_DISCOUNT",

  "RETURN_DISC_AMT",

  "TRANS_ID"

FROM "XXOIC"."XXOIC_LAC_RETURN_HEADERS";

INNER JOIN (XXOIC_LAC_RETURN_HEADERS)

LOAD ORG_ID,

  ORGANIZATION_ID,

  INVENTORY_ITEM_ID,

  HEADER_ID,

  ATTRIBUTE5,

  RETURN_QTY*-1 as ORDER_QTY,

  TRANS_ID,

  PRICE_AFTER_RTRN_DISC as PRICE_AFTER_DISCOUNT,

  DO_RETURN_DATE as ORDERED_DATE,

    TRASACTION_ID AS TRANSACTION_ID,

  ITEM_COST;

[XXOIC_LAC_RETURN_LINES]:

SELECT "ORG_ID",

  "ORGANIZATION_ID",

  "INVENTORY_ITEM_ID",

  "HEADER_ID",

  "ATTRIBUTE5",

  "RETURN_QTY" ,

  "TRANS_ID",

  "PRICE_AFTER_RTRN_DISC" ,

  "DO_RETURN_DATE",

  "TRASACTION_ID",

  "ITEM_COST"

FROM "XXOIC"."XXOIC_LAC_RETURN_LINES";

INNER JOIN (XXOIC_LAC_RETURN_HEADERS)

LOAD TRANSACTION_ID,

  TRANSACTION_DATE;

[MTL_MATERIAL_TRANSACTIONS]:

SELECT "TRANSACTION_ID",

  "TRANSACTION_DATE"

FROM "INV"."MTL_MATERIAL_TRANSACTIONS"

WHERE TRANSACTION_ID >148100175;

CONCATENATE(XXOIC_OE_ORDER_HEADERS_ALL)

LOAD * RESIDENT XXOIC_LAC_RETURN_HEADERS;

DROP Table XXOIC_LAC_RETURN_HEADERS;

Note: Net_Amount formula: (Order_Qty*NVL(PRICE_AFTER_DISCOUNT,NET_UNIT_SELLING_PRICE))

Meantime I will also try my level best to implement the same.

Thanks

[#4]

Hi Kushal,

I already mentioned in my post question that I tried this method & It's not working.

[svinnakota]

Hi,

Based on the output which you mentioned earlier.

Order No.          Date                         Amount          Net_amt

1                    1/1/2000                       -10                    15

Take a table in the dashboard and apply these things over there.

Dimension: Order No, Subfield(Date,' ',1)['space'] , Amount

Measure : Sum(Net_amt)

I am unable to upload QWF. Please let me know your comments.

[#6]

Hi Santosh,

Thanks for your reply.

I want amount column to consider as Measure. Because I have to get grand total of it.

[svinnakota]

Hi,

Please find the attached QVF. I made the Amount as measure. Please check the QVF and let me know your comments.

I made the Amount as Measure.

[#8 · Accepted solution]

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.

[svinnakota]

Welcome Sandesh,

Try to Consider Sum(Distinct Amount)+Sum(Distinct Return_Amount) in the expression.

Let me know whether the issue is resolved / not. If not please attach the file with dummy data. So that we can check and try to get the required output.

Thanks,

Santosh Aravind.

[#10]

Yes, I already considered Sum(Distinct Amount)+Sum(Distinct Return_Amount) in the expression.

The issue is been resolved now. Thanks a ton for all of your support.