How to SUM a range of date?

[#1]

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

[#2]

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

[sinanozdemir]

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

See if the attached works for you.

Thanks

[#4]

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;

[#5]

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

[sinanozdemir]

Hi Daniel,

Something like this:

[#7]

Yes! That Works great! Somehow I don't know what I did wrong the first time, but now it Works!!!

Thanks a lot for your help!!! 😃

[sinanozdemir]

No problem. Any time.