Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
DipeshVadgama
Partner
Partner

Canonical Date Vs filters issue

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 

Need Help.PNG

Labels (1)
6 Replies
PriyankaShivhare
Creator II
Creator II

may be u need to use DateType in your expression

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

 

Thanks,

Priyanka

DipeshVadgama
Partner
Partner
Author

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

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

 

PriyankaShivhare
Creator II
Creator II

i cannot view the QVW. can u share ur script

DipeshVadgama
Partner
Partner
Author

[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
Partner
Partner
Author

Hi,

Please some one help me on this issue.

Rodj
Luminary Alumni
Luminary Alumni

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