Canonical Date Vs filters issue

[DipeshVadgama]

Hi,

I have facing issue with filters & calculation in my projects. I have to use canonical date  as  i have multiple dates in tables for customer request.

I have successfully created canonical date table, but when i apply filters or cross check my calculation it does not.

Example when i apply filter as west in region it give me weird chart and sum of quality also doesn't match the pivots in excel file.

Please can any one help me what i am doing wrong. Canonical Date 

[PriyankaShivhare]

may be u need to use DateType in your expression

sum({<DateType ={'Order'}>}OrderLineAmount)

Thanks,

Priyanka

[DipeshVadgama]

I have a same expression but still i have a above issue.

=Sum({<DateType={'Order'}>}Quality)

[PriyankaShivhare]

i cannot view the QVW. can u share ur script

[DipeshVadgama]

[Orders]:
LOAD OrderDate,
OrderID,
RequiredDate,
CustomerID,
ShipperID,
Quality,
Region

FROM
[C:\Users\dipes\Downloads\Orders.xlsx]
(ooxml, embedded labels, table is Sheet1);

MC_Orders_OrderDate:
Load
OrderDate As OrderDate,
Year(OrderDate) As OrderYear,
Month(OrderDate) As OrderMonth ;

Load
Date(MinOrderDate + IterNo() - 1) As OrderDate
While MinOrderDate + IterNo() - 1 <= MaxOrderDate;

Load
Date(Min(FieldValue('OrderDate',RecNo()))) As MinOrderDate,
Date(Max(FieldValue('OrderDate',RecNo()))) As MaxOrderDate
AutoGenerate FieldValueCount('OrderDate');


MC_Orders_RequiredDate:
Load
RequiredDate As RequiredDate,
Year(RequiredDate) As RequiredDateYear,
Month(RequiredDate) As RequiredDateMonth ;

Load
Date(MinRequiredDate + IterNo() - 1) As RequiredDate
While MinRequiredDate + IterNo() - 1 <= MaxRequiredDate;

Load
Date(Min(FieldValue('RequiredDate',RecNo()))) As MinRequiredDate,
Date(Max(FieldValue('RequiredDate',RecNo()))) As MaxRequiredDate
AutoGenerate FieldValueCount('RequiredDate');

-------------------------------------------------------------------------------------------------------------

[OrderLines]:
LOAD ShippedDate,
OrderID,
OrderLineID,
ProductID,
Delivery
FROM
[C:\Users\dipes\Downloads\OrderLines.xlsx]
(ooxml, embedded labels, table is Sheet1);

MC_OrderLines_ShippedDate:
Load
ShippedDate As ShippedDate,
Year(ShippedDate) As ShippedDateYear,
Month(ShippedDate) As ShippedDateMonth ;

Load
Date(MinOrderDate + IterNo() - 1) As ShippedDate
While MinOrderDate + IterNo() - 1 <= MaxOrderDate;

Load
Date(Min(FieldValue('ShippedDate',RecNo()))) As MinOrderDate,
Date(Max(FieldValue('ShippedDate',RecNo()))) As MaxOrderDate
AutoGenerate FieldValueCount('ShippedDate');

-------------------------------------------------------------------------------------------------------------

OrderID2OrderDate:
Mapping Load OrderID, OrderDate FROM
[C:\Users\dipes\Downloads\Orders.xlsx]
(ooxml, embedded labels, table is Sheet1);

OrderID2RequiredDate:
Mapping Load OrderID, RequiredDate FROM
[C:\Users\dipes\Downloads\Orders.xlsx]
(ooxml, embedded labels, table is Sheet1);

DateBridge:
Load
[OrderLineID],
Applymap('OrderID2OrderDate',OrderID,Null()) as CanonicalDate,
'Order' as DateType
Resident [OrderLines];

Load
[OrderLineID],
ShippedDate as CanonicalDate,
'Shipped' as DateType
Resident [OrderLines];

MC_OCanonicalDate:
Load
CanonicalDate As CanonicalDate;
Load
Date(MinCanonicalDate+ IterNo() - 1) As CanonicalDate
While MinCanonicalDate + IterNo() - 1 <= MaxCanonicalDate;

Load
Date(Min(FieldValue('CanonicalDate',RecNo()))) As MinCanonicalDate,
Date(Max(FieldValue('CanonicalDate',RecNo()))) As MaxCanonicalDate
AutoGenerate FieldValueCount('CanonicalDate');

[DipeshVadgama]

Hi,

Please some one help me on this issue.

[Rodj]

Hi @DipeshVadgama,

If I may ask, what are you trying to achieve with your various MC_* tables? I can't see the value in them and I think they may just be confusing the issue. Have you seen this article? It breaks the process of creating a canonical date down fairly simply using a data model very similar to the one you are working with.

I should also point out that you've posted in the new to Qlik Sense board when you appear to be using QlikView, not that it really matters with this type of query.

Cheers,

Rod