Issue with Multiple Date dimensions, How to handle... - Qlik Community

Correct_Answer · ‎2024-10-22

Hi Teams,

I have a 2 DataSets, one is with contracts information as shown below

Contracts Overview Table
Contract_ID	Contract_Date	Contrat_MONTH	Contract_Year	Contract_Value	Start_Date	End_Date
1001	23-Jan-23	JAN	2023	10000	23-Jan-23	1-Dec-23
1002	1-Feb-22	Feb	2022	20000	1-Feb-22	1-Dec-23
1003	1-Mar-24	MAR	2024	4622	1-Mar-24	1-Dec-24

Here, it shows the details one record for one Contract_ID.

and I have another table for Sales contribution across the Year Month contribution value as shown below.

Sales Utilization:

Contract ID	SALES Date	SALES MONTH	SALES Year	Sales Value
1001	23-Jan-23	JAN	2023	5000
1001	########	FEB	2023	2000
1001	########	MAR	2023	1000
1001	23-Apr-23	APR	2023	2000
1002	1-Feb-22	Feb	2022	5000
1002	1-Mar-22	MAR	2022	5000
1002	1-Apr-22	APR	2022	1000
1002	1-May-22	MAY	2022	1000
1002	1-Jun-22	JUN	2022	4000
1002	1-Jul-22	JUL	2022	1000
1002	1-Aug-21	AUG	2021	500
1002	1-Sep-21	SEP	2021	1500
1002	1-Oct-21	OCT	2021	1000
1003	1-Mar-24	MAR	2024	2500
1003	1-Apr-24	APR	2024	100
1003	1-May-24	MAY	2024	120
1003	1-Jun-24	JUN	2024	400
1003	1-Jul-24	JUL	2024	50
1003	1-Aug-24	AUG	2024	90
1003	1-Sep-24	SEP	2024	18
1003	1-Oct-24	OCT	2024	15
1003	1-Nov-24	NOV	2024	20
1003	1-Dec-24	DEC	2024	900
1003	1-Jan-25	JAN	2024	409

I wants to create a view to show the Contract and Sales values in a Pivot table for comparison.

But data is not showing the correct values if I connect the these tables by using Contract_ID.

I want to create the MasterCalendar to Join these tables,

By Selecting the Year and Month like: Feb and 2022 the Contract_Value should be 20000 and Sales_Value should be 5000.

in this case, the Sales_values are missing from sale table because it splitting across months. in this sinario how to handle the data.

What are the best practices to compare sales and contract values over the time dimention.

Can any one help with possible approaches.

Your help will be much appreciated.

Regards,

Qrishna · ‎2024-10-22

Try below:

Contracts:
Load *, %ContractDateKey as %DateKey;
Load *, (Contract_ID & '|' & Date#(Contract_Date, 'DD-MMM-YYYY')) as %Key, Num(Date#(Contract_Date, 'DD-MMM-YYYY')) as %ContractDateKey;
LOAD * inline [
Contract_ID,Contract_Date ,Contrat_MONTH,Contract_Year,Contract_Value,Start_Date,End_Date
1001,23-Jan-23,JAN,2023,10000,23-Jan-23,1-Dec-23
1002,1-Feb-22,Feb,2022,20000,1-Feb-22,1-Dec-23
1003,1-Mar-24,MAR,2024,4622,1-Mar-24,1-Dec-24
];

Contract_Details:
Load *, ([Contract ID] & '|' & Date#([SALES Date], 'DD-MMM-YYYY')) as %Key, Num(Date#([SALES Date], 'DD-MMM-YYYY')) as %SalesDateKey;
LOAD [Contract ID],
[SALES Date],
[SALES MONTH],
[SALES Year],
[Sales Value]
FROM
[https://community.qlik.com/t5/App-Development/Issue-with-Multiple-Date-dimensions-How-to-handle-the-...]
(html, codepage is 1252, embedded labels, table is @2);

//----------------------------------------------------------------------------//
//Prepare Calendar from the above dates
Calendar_Temp:
Load %ContractDateKey as Date
resident Contracts
where not wildMatch(%ContractDateKey, '*#*');
Load %SalesDateKey as Date
resident Contract_Details
where not wildMatch(%SalesDateKey, '*#*');

MinMax:
LOAD
Min(Date) as MinDate,
Max(Date) as MaxDate
RESIDENT Calendar_Temp;

let vMinDate = num(peek('MinDate', 0, 'MinMax'));
let vMaxDate = num(peek('MaxDate', 0, 'MinMax'));

drop table MinMax;

Calendar:
Load
Num(Date(DateNum, 'DD-MMM-YYYY')) as %DateKey,
Date#(Date(DateNum), 'DD-MMM-YYYY') as Date,
Month(DateNum) as Month,
Year(DateNum) as Year;
Load distinct
$(vMinDate) + IterNo() - 1 as DateNum
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

drop table Calendar_Temp;

Correct_Answer · ‎2024-10-30

Hi @Qrishna

Thanks for taking time to provide your solution, it is possible in 2 different views one for sales and another for contracts.

But , User expecting the both(Sales and Contracts) on the same pivot view to easy to compare the sales and contract net values. any how the sales values splitting across the months and years but If we select Contract table month year then it show show the contract value associated contract and associated sales value from sales table. and again user wants to drill down further the sales value should be reduced but not Contracts values. it remains same until we select the Contract Year and Month and Sales value remains same until we select the sales year and month filters. by creating any common table.

as shown below.

Can you please help in this scenario.

Your help will be much appreciated. Thanks

marcus_sommer · ‎2024-10-30

Fact-tables couldn't be directly associated - at least not without some risks to lose any information in the various views.

This means additional steps are required, for example to check both sides against each other in beforehand and then to fill per data-population the gaps. Another common way is to place a link-table between them.

Both approaches have disadvantages in regards to efforts, complexity, performance and further more and therefore I suggest to skip all these challenges by using a star-scheme data-model and combining the facts into a single fact-table by concatenating them and harmonizing all field-names and data-structures as much as possible, for example in this way with A = Contracts and B = Sales:

t: load ID, date(From + iterno() - 1) as Date, Value / (To - From + 1) as Value, 'A' as Source
from A while From + iterno() - 1 <= To;

concatenate(t)

load ID, Date, Value, 'B' as Source
from B;

Correct_Answer · ‎2024-11-02

Hi @marcus_sommer

Thanks for your response, we can concatenate the two tables as per your solution but there a challenge while comparing the 2 values as shown below.

Contracts:
LOAD [Contract ID],
[Contract Date],
[Contrat MONTH],
[Contract Year],
[Contract Value],
Start_Date,
End_Date,
'A' as Source
FROM
[C:\Users\Srini\OneDrive\Desktop\Source for Contract Sales.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Concatenate(Contracts)

Sales:

LOAD [Contract ID],
[SALES Date] as [Contract Date],
[SALES MONTH] as [Contrat MONTH],
[SALES Year] as [Contract Year],
[Sales Value],
'B' as Source
FROM
[C:\Users\Srini\OneDrive\Desktop\Source for Contract Sales.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet2);

Following is the output :

Blanks are populating as per the Data on Sales Year and Month Dimension,

While comparing the Contract Overall amount and sales distributed amount, Sum(Contract_values) showing zero for few selection, which are booking date is different than sales distribution date.

for Example:

Contract values are coming as zero for year month selection because of concatenation so that we are unable to compare the overall contract value with Breakdown sales values.

Can you please help to achieve this scenario

Your help will be much appreciated.

Regards,

marcus_sommer · ‎2024-11-02

That wasn't my suggestion else that all fields all harmonized as much as possible. This mewans there is only one ID and one Date and one Value and not separated ones for each source. All these data are the same only the direction of them is different which is addressed with the extra field Source - which enables you to differentiate between the sources in dimensions, selections and set analysis condition.

Kushal_Chawda · ‎2024-11-03

@Correct_Answer As your contract table has single row per ID you can join it with sales table on contract_id and monthstart date (sales_date & contract_date). If your sales_date & contract_date are matching then no need to create MonthStart, you can directly join contract_date with sales_date by renaming it

sales:
Load Contract_ID,
SALES_Date,
MonthStart(SALES_Date) as MonthStart,
Sales_Value;
load * Inline [
Contract_ID SALES_Date SALES_MONTH SALES_Year Sales_Value
1001 23-Jan-23 JAN 2023 5000
1001 1-Feb-23 FEB 2023 2000
1001 11-Mar-23 MAR 2023 1000
1001 23-Apr-23 APR 2023 2000
1002 1-Feb-22 Feb 2022 5000
1002 1-Mar-22 MAR 2022 5000
1002 1-Apr-22 APR 2022 1000
1002 1-May-22 MAY 2022 1000
1002 1-Jun-22 JUN 2022 4000
1002 1-Jul-22 JUL 2022 1000
1002 1-Aug-21 AUG 2021 500
1002 1-Sep-21 SEP 2021 1500
1002 1-Oct-21 OCT 2021 1000
1003 1-Mar-24 MAR 2024 2500
1003 1-Apr-24 APR 2024 100
1003 1-May-24 MAY 2024 120
1003 1-Jun-24 JUN 2024 400
1003 1-Jul-24 JUL 2024 50
1003 1-Aug-24 AUG 2024 90
1003 1-Sep-24 SEP 2024 18
1003 1-Oct-24 OCT 2024 15
1003 1-Nov-24 NOV 2024 20
1003 1-Dec-24 DEC 2024 900
1003 1-Jan-25 JAN 2024 409](delimiter is '\t');

Left Join(sales)
Load Contract_ID,
MonthStart(Contract_Date) as MonthStart,
Contract_Date,
Contract_Value,
Start_Date,
End_Date;
load * Inline [
Contract_ID Contract_Date Contrat_MONTH Contract_Year Contract_Value Start_Date End_Date
1001 23-Jan-23 JAN 2023 10000 23-Jan-23 1-Dec-23
1002 1-Feb-22 Feb 2022 20000 1-Feb-22 1-Dec-23
1003 1-Mar-24 MAR 2024 4622 1-Mar-24 1-Dec-24 ](delimiter is '\t');

Calendar:
Load *,
monthname(SALES_Date) as MonthName,
month(SALES_Date) as Month;
Load date(MinDate+IterNo()-1) as SALES_Date
While MinDate+IterNo()-1<=MaxDate;
Load min(Date) as MinDate,
max(Date) as MaxDate;
Load FieldValue('SALES_Date',RecNo()) as Date
AutoGenerate FieldValueCount('SALES_Date');

For, contract_value, you might need to use sum(distinct contract_value), because in sales you might have multiple entries in a month for same contract_id, hence you will have repeated contract_values for that month. If you have single entry of sales for that month for same contract_id, no need to use distinct

Note: when you use sum(distinct contract_value), make sure for that measure you have enabled Total option as "Sum"

Issue with Multiple Date dimensions, How to handle the Data over the multiple time dimensions.

General Question