This is the file example. Not sure what you mean with Data Modeling issue.
Date.xlsx 57.0 K
Discount Sales - Sinan.qvf 192.0 K
The below was your original data model:
The syn table occurs when there are multiple joins or circular references:
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:
In the load script, I also dropped the two common fields of Item ID and Products from Dates table:
and renamed the unit price field in Sales table to sales unit price to avoid the syn table or any other circular references.
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:
If this helps, please close the thread by choosing one of the answers as correct or helpful.
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
Forum.xlsx 325.9 K
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
[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;
Discount Sales.qvf 320.0 K
You can easily do that by selecting Tiendas and Productos tables from the excel sheet:
See if the attached works for you.
Interval Match.qvf 288.0 K
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.
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]
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]
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]
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]
Date(Date#(PERIODOID, 'YYYYMMDD'), 'MM/DD/YYYY') & '|' & ITEM_ID As Key2,
STORE_ID as "Store ID",
Date(Date#(PERIODOID, 'YYYYMMDD'), 'MM/DD/YYYY') As Fecha,
"Total Cantidad Peso Vendido",
FROM [lib://QVDs_Connection (gr-hq-bi-02_qs-admin)/SalesDetails_REY_*.QVD]
(qvd) WHERE PERIODOID>=20131001;
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",
PT_TYPE & PT_TYPE_DESCRIPTION as "Price Type",
V_ID as "Vendor ID"
FROM [lib://QVDs_Connection (gr-hq-bi-02_qs-admin)/ItemPrice.QVD]
(qvd) WHERE STORE_ID<>'0';
"Discount Start Date",
"Discount End Date",
"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;
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.
Discount.xlsx 15.0 K