Two different Dates

[Anonymous]

Hi All,

I have a table with two Dates open Date and Closed Date and a master calendar,

Now I wanted to show a bar chart with

Dimension : Date (From master Calendar)

and

Expression :1)  No of open defect on that particular date

                     2) No of close defects on that particular date.

Thanks,

[sunny_talwar · Accepted solution]

Use Canonical Calendar here: Canonical Date

[MK_QSL]

Data:

Load

  OrderNumber,

  OrderDate,

  DeliveryDate;

From TableName;

Map_OrderDate:

Mapping Load OrderNumber, OrderDate Resident Data;

Map_DeliveryDate:

Mapping Load OrderNumber, DeliveryDate Resident Data;

BridgeTable:

Load OrderNumber, ApplyMap('Map_OrderDate', OrderNumber, Null()) as CanonicalDate, 'Created' as Type Resident Data;

Load OrderNumber, ApplyMap('Map_DeliveryDate', OrderNumber, Null()) as CanonicalDate, 'Delivered' as Type Resident Data;

Left Join (BridgeTable)

Load Distinct CanonicalDate, Month(CanonicalDate) as Month, Year(CanonicalDate) as Year Resident BridgeTable;

Straight Table

Dimension

CanonicalDate

Expression

COUNT({<Type = {'Created'}>}DISTINCT OrderNumber)

COUNT({<Type = {'Delivered'}>}DISTINCT OrderNumber)

Use the same logic in your app.