16 Replies Latest reply: Nov 16, 2015 2:05 PM by Sinan Ozdemir RSS

    How to SUM a range of date?

      Hello,

       

      I am trying to créate a report of Discount Sales using Qlik Sense.

       

      How can I add up a specified range of date?

       

      Example in file.

       

      Once you open file with Qlik Sense, you will see the following columns:

       

      Date / Ítem ID / Products / Discount End Date / SalePrice / DiscPrice / Quantity / NetSales

       

      I want to add or sum all Quantity and NetSales between the Date and Discount End Date of the Sales Date.

       

      For Example:

       

      Product 1 of Ítem ID 10001, the Discount Date runs from Sept 1st to Sept 7th, if I add up all the sales total from that range gives me $17.00 from 34 unit sold.

       

      But if I just want to see the discount date, I uncheck the Show null values from Discount End Date, and this gives me one line data. and the Total need to be $17 and 34 Quantity, but this shows me 1 unit which is $.50 cents

       

      I attached the App

        • Re: How to SUM a range of date?
          Sinan Ozdemir

          Hi,

           

          You have some data modelling issues. Can you post the excel files here as well?

           

          Thanks

            • Re: How to SUM a range of date?

              Hi Sinan,

               

              This is the file example. Not sure what you mean with Data Modeling issue.

               

              thanks

                • Re: How to SUM a range of date?
                  Sinan Ozdemir

                  Is this what you are looking for?

                  Capture.PNG

                   

                  I am attaching the qvf.

                   

                  Hope this helps.

                   

                  Thanks

                    • Re: How to SUM a range of date?

                      Hi Sinan,

                       

                      This is really helpful. Sincé I am new with Qlik, can you explain me what exactly I did wrong or how did you do it?

                       

                      Regards.

                       

                      Daniel

                        • Re: How to SUM a range of date?
                          Sinan Ozdemir

                          Hi Daniel,

                           

                          The below was your original data model:

                          Capture.PNG

                          The syn table occurs when there are multiple joins or circular references:

                          Capture.PNG

                          As you can see you had Item ID and Products in both Discount and Sales table. Also the discount table was really a reference table which had date ranges for different products. In this case, I used IntervalMatch() function to find the appropriate discounts for products that had the same date range.

                          IntervalMatch() function helped to create the bridge table to connect Discount and Sales by using date ranges and Products:

                          Capture.PNG

                          In the load script, I also dropped the two common fields of Item ID and Products from Dates table:

                          Capture.PNG

                          and renamed the unit price field in Sales table to sales unit price to avoid the syn table or any other circular references.

                          Capture.PNG

                          Finally, in the front-end table, I used Aggr() function to aggregate Net Sales, Quantity, and Date fields to get one record per product view. You can check the functions and use of dimensions within Aggr() functions:

                          Capture.PNG

                           

                          If this helps, please close the thread by choosing one of the answers as correct or helpful.

                           

                          Thanks

                            • Re: How to SUM a range of date?

                              Hi Sinan,

                               

                              I am trying this on another QlikSense App and don't know what I'm doing wrong.

                               

                              I attached the Excel File with the column name that is in use, I just created the rows.

                               

                              trying to apply the same thing that you teached me, but not working for me, when I come to this informations.

                               

                              Thanks for your help

                               

                              Regards,

                              • Re: How to SUM a range of date?

                                sinanozdemir

                                Thanks for your help before, but I am encountering the same problem.

                                 

                                I have attached the qvf and the Excel file.

                                 

                                I tried to do the Interval Match, but don't really know how it Works.

                                 

                                I did this, and say that field doesn't exist.

                                 

                                hope you can help me

                                 

                                Thanks in advance for your help

                                 

                                 

                                 

                                 

                                [Interval Match]:
                                IntervalMatch(num([PeriodoReal]),[ITEM_ID])
                                LOAD
                                [IP_START_DATE],
                                    [IP_END_DATE],
                                    [ITEM_ID]
                                Resident [ItemPrice];

                                Bridge:
                                LOAD
                                [ITEM_ID] & '|' & [IP_START_DATE] & '|' & [IP_END_DATE] As Key1,
                                    [ITEM_ID] & '|' & num(date#([PERIODOID],'YYYYMMDD')) As Key2
                                Resident [Interval Match];

                                Drop Table [Interval Match];
                                Drop Fields "Item ID", [DESCRIPTION_PRODUCT] From Dates;

                                  • Re: How to SUM a range of date?
                                    Sinan Ozdemir

                                    Hi Daniel,

                                     

                                    I will take a look at it.

                                     

                                    Thanks

                                    • Re: How to SUM a range of date?
                                      Sinan Ozdemir

                                      Hi Daniel,

                                       

                                      Sorry for the late reply. I only brought two tables for the simplicity sake:

                                      Capture.PNG

                                       

                                      Take a look at the attached qvf and let me know if it works for you.

                                        • Re: How to SUM a range of date?

                                          Hi Sinan,

                                           

                                          I will need to filter this by the Store Name, instead of the Store ID, and filter by product name too. At least I will need the Store and Product tab there.

                                           

                                          Regards

                                            • Re: How to SUM a range of date?
                                              Sinan Ozdemir

                                              You can easily do that by selecting Tiendas and Productos tables from the excel sheet:

                                              Capture.PNG

                                               

                                              See if the attached works for you.

                                               

                                              Thanks

                                                • Re: How to SUM a range of date?

                                                  Hi Sinan,

                                                   

                                                  I am not sure what I've done wrong, comparing with the QVF file you sent me.

                                                   

                                                  Below are the info I added, seems to load perfectly, but, if I get a a table for Key1 and Key2, doesn't match up.

                                                   

                                                  Hope you can understand what I have here. I don't have the QVD files available to share.

                                                   

                                                  Periodo:
                                                  LOAD
                                                      PERIODOID as "Periodo ID",
                                                      Periodo as "Año",
                                                      PeriodoMes as "Mes",
                                                      PeriodoDia as "Dia",
                                                      PeriodoDiaSemana as "Dia de la Semana",
                                                      PeriodoSemana as "Semana",
                                                      date(floor(PeriodoReal),'MM/DD/YYYY') as "Fecha",
                                                      PeriodoFiscal as "Año Fiscal",
                                                      'Q'&PeriodoFiscalTrimestre as "Trimestre Fiscal",
                                                      PeriodoMesFiscal & ' (' & PeriodoMesDesc & ')' as "Mes Fiscal",
                                                      PeriodoSemanaFiscal as "Semana Fiscal",
                                                      PeriodoMesDesc as "Mes Desc.",
                                                      PeriodoDiaSemanaDesc as "Dia de Semana Desc."
                                                  FROM [lib://QVDs_Connection (gr-hq-bi-02_qs-admin)/PERIODOS.QVD]
                                                  (qvd);

                                                   

                                                  Productos:
                                                  LOAD
                                                      ITEM_ID,
                                                      UPC_EAN as "UPC EAN",
                                                      Capitalize(DESCRIPTION_PRODUCT) as "Producto",
                                                      SEL_UNIT_OF_MEASURE as "Unidad de Medida",
                                                      SELL_SIZE as "Tamaño de Medida",
                                                      Capitalize(DESCRIPTION_DEPARTMENT) as "Departamento",
                                                      Capitalize(DESCRIPTION_BRAND) as "Marca",
                                                      Capitalize(DESCRIPTION_CATEGORY) as "Categoria del Depto",
                                                      Capitalize(DESCRIPTION_CLASE) as "Clase",
                                                      SEGMENT as "Segmento",
                                                      IF(Area_Comercial_Merma <> 'CARNES' AND Area_Comercial_Merma <> 'EMBUTIDOS' AND Area_Comercial_Merma <> 'FRUTAS FRESCAS IMPORTADAS' AND Area_Comercial_Merma <> 'FRUTAS FRESCAS NACIONALES' AND Area_Comercial_Merma <> 'OTROS DEPARTAMENTOS' AND Area_Comercial_Merma <> 'OTROS IMPORTADOS' AND Area_Comercial_Merma <> 'PANES Y DULCES FRESCOS DULCERIA' AND Area_Comercial_Merma <> 'QUESOS' AND Area_Comercial_Merma <> 'VEGETALES FRESCOS IMPORTADOS' AND Area_Comercial_Merma <> 'VEGETALES FRESCOS NACIONALES','Sin Categoria',Capitalize("Area_Comercial_Merma")) as "Familia",
                                                      IF(rey_track_sales='s','Si','No') as "Rey Track Sales",
                                                      Capitalize(Area_Frescura) as "Area Frescura",
                                                      Capitalize(Area_Boletin) as "Categoria",
                                                      PADRE_O_NO as "Padre o No"
                                                  FROM [lib://QVDs_Connection (gr-hq-bi-02_qs-admin)/PRODUCTOS.QVD]
                                                  (qvd);


                                                  Proveedores:
                                                  LOAD
                                                      V_ID as "Vendor ID",
                                                      VENDOR as "Vendor Code",
                                                      Capitalize(VENDOR_NAME) as "Vendor Name",
                                                      IF(Casa='Si','Si','No') as "Proveedor de Casa"
                                                  FROM [lib://QVDs_Connection (gr-hq-bi-02_qs-admin)/PROVEEDORES.QVD]
                                                  (qvd);


                                                  Tiendas:
                                                  LOAD
                                                      STORE_ID,
                                                      Capitalize(STORE_NAME) as "Store Name",
                                                      Capitalize(STORE_GROUP) as "Store Group",
                                                      Capitalize(REGION_GROUP) as "Region Group",
                                                      PRM_STORE_NUMBER as "Store Number"
                                                  FROM [lib://QVDs_Connection (gr-hq-bi-02_qs-admin)/TIENDAS.QVD]
                                                  (qvd);


                                                  Ventas:
                                                  LOAD
                                                      Date(Date#(PERIODOID, 'YYYYMMDD'), 'MM/DD/YYYY') & '|' & ITEM_ID As Key2,
                                                      V_ID,
                                                      STORE_ID as "Store ID",
                                                      ITEM_ID,
                                                      Date(Date#(PERIODOID, 'YYYYMMDD'), 'MM/DD/YYYY') As Fecha,
                                                      ORIGEN,
                                                      "Sales Q",
                                                      "Cantidad Vendida",
                                                      "Peso Vendido",
                                                      "Costo Venta",
                                                      "Total Cantidad Peso Vendido",
                                                      "Venta Neta",
                                                      Descuento,
                                                      "Descuento Empleado",
                                                      CASE_PACK,
                                                      OUTLIER  
                                                  FROM [lib://QVDs_Connection (gr-hq-bi-02_qs-admin)/SalesDetails_REY_*.QVD]
                                                  (qvd) WHERE PERIODOID>=20131001;


                                                  ItemPrice:
                                                  LOAD
                                                  date(floor(IP_START_DATE),'MM/DD/YYYY') & '|' & date(floor(IP_END_DATE),'MM/DD/YYYY') & '|' & ITEM_ID As Key1,
                                                      date(floor(IP_END_DATE),'MM/DD/YYYY') as "Discount End Date",
                                                      date(floor(IP_START_DATE),'MM/DD/YYYY') as "Discount Start Date",
                                                      IP_UNIT_PRICE as "Unit Price",
                                                      ITEM_ID,
                                                      PT_TYPE & PT_TYPE_DESCRIPTION as "Price Type",
                                                      STORE_ID,
                                                      UCOST_WITH_ADJUSTMENTS,
                                                      V_ID as "Vendor ID"

                                                  FROM [lib://QVDs_Connection (gr-hq-bi-02_qs-admin)/ItemPrice.QVD]
                                                  (qvd) WHERE STORE_ID<>'0';


                                                  [Interval Match]:
                                                  IntervalMatch(Fecha, ITEM_ID)
                                                  LOAD
                                                      "Discount Start Date",
                                                      "Discount End Date",
                                                      ITEM_ID
                                                  Resident ItemPrice;

                                                  Bridge:
                                                  LOAD
                                                  "Discount Start Date" & '|' & "Discount End Date" & '|' & ITEM_ID As Key1,
                                                      Fecha & '|' & ITEM_ID As Key2
                                                  Resident [Interval Match];

                                                  Drop Table [Interval Match];
                                                  Drop Field ITEM_ID From ItemPrice;

                                                  • Re: How to SUM a range of date?

                                                    Hi Sinan,

                                                     

                                                    I finally got it... but having trouble doing the calculation.

                                                     

                                                    I am using the AGGR function as you told me...

                                                     

                                                    Aggr(Sum([Cantidad de Venta]),Producto, [Discount End Date])

                                                     

                                                     

                                                    I attached the file in Excel, since I can't bring the QVF file.

                                                    In the App, shows only one line with the Cantidad de Venta with the total sales which is 109. But if I click on the Store X, it will show correct information. I tried to add Store Name in the formula, but gives 109 to all the rows. how can I fix this? sorry to bother you a lot.

                                                     

                                                    thanks in advance for you help.

                                                     

                                                    Regards