20 Replies Latest reply: Aug 30, 2016 4:00 AM by sandesh ag RSS

    How to get distinct sum based on date.

    sandesh ag

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

        • Re: How to get distinct sum based on date.
          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)

            • Re: How to get distinct sum based on date.
              balraj ahlawat

              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?

                • Re: How to get distinct sum based on date.
                  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.

                    • Re: How to get distinct sum based on date.
                      sandesh ag

                      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

                        • Re: How to get distinct sum based on date.
                          balraj ahlawat

                          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;

                            • Re: How to get distinct sum based on date.
                              sandesh ag

                              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.

                            • Re: How to get distinct sum based on date.
                              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)

                                • Re: How to get distinct sum based on date.
                                  sandesh ag

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

                                    • Re: How to get distinct sum based on date.
                                      Puttemans Johan

                                      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;

                                        • Re: How to get distinct sum based on date.
                                          sandesh ag

                                          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

                            • Re: How to get distinct sum based on date.
                              kushal chawda

                              try this

                               

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

                              • Re: How to get distinct sum based on date.
                                Santosh Vinnakota

                                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.