Multiple dates fields

[#1]

Hi,

I'm a new in qlikview and do my first project.

I've created database for fictitious web store with randomized records.

I'm stuck in financial raports.

I'v done tables like income, salaries etc.

However, I can't do a table like "profit", because I've different date source for each field.

I was trying make common calendar like here: Tutorial - Using Common Date Dimensions and Shared Calendars

Unfortunately, my dates aren't lineked on one field, so I cant use these method.

I have tried hadle with this problem for several days and currently I'm out of any ideas.

That's why I ask you for help.

I've attached my project.

Regards,

Maciej

[Chip_Matejowsky]

Hello Maciej,

It appears you are requesting assistance with QlikView. This is the Qlik Sense forum. Please post your question to the appropriate QlikView forum. Thank you.

[Josh_Good]

I'll move the discussion for you.

-Josh

[jaimeaguilar]

Hi,

you can use makedate() function in order to get a complete date that you can use to create a master calendar (or as many as you need.

For example in your case you can create a new field like this:

MakeDate(ORDERS.order_date_Year, ORDERS.order_date_month, ORDERS.order_date_day)

Also this field can serve as basis for creating a master calendar,

regards

[#5]

Hi,

I advice you think in a star schema with a fact table created with Orders and Order_Details (you can use a Left Join, first load Order and then left join Order_Details), I guess your main Date Field must be created with MakeDate() Function using Year,Month and Day Fields (I guess Order_Date_Year/Month or Day) and based on this calculated field create a Master Calendar.

Something like this:

Orders:

LOAD

     ID_Order,

     ID_Employee,

     ID_Customer,

     Date(

     MakeDate(Order_Date_Year,Order_Date_Month,Order_Date_Day),'YYYY-MMM-DD') as OrderDate,

     ... // Rest of your Fields

From Orders.qvd;

LeftJoin(Orders)

Order_Detail:

LOAD

     ID_Order,

     ID_Product,

     Amount

From Order_Details.qvd;

With above script you can create your fact table and then just use Order date to create Master Calendar because your QV app is for analysing Sales so the main Date Field is Order Date, the others Dates in the rest of your table are atributes not a representative Dimension like Orde Date.

MasterCalendar:

LOAD Distinct

     OrderDate,

     Year(OrderDate) as Year,

     Month(OrderDate) as Month,

     Day(OrderDate) as Day,

     Week(OrderDate) as Week,

     MonthName(OrderDate) as Period

Resident Orders;

The rest of your table are Dimensions and others are Atributes of that tables, tomorrow I try to give you your QVW with this.

Best regards.

[#6]

Hi Oswaldo,

Thanks for your response.

Unfortunately, this doesn't solve my problem.

In my table "Profit" I need calculate something like that: Sales - (Supplies + Salaries)

Each of them has different sources of dates(Sales - Orders.end_date, Supplies - Supply_date, Salaries - Salary.Date ).

I think it needs to combine it into a single calendar, but I don't know how to do that.

Regards,

Maciej

[#7]

HI,

Well if you need to take Date Fields in Orders and Date Fields in Supplies you can try to rename that fields in both tables (Orders and Supplies) with the same Name and concatenate two tables. Something like this:

Orders:

LOAD

     ID_Order,

     ID_Employee,

     ID_Customer,

     Date(

     MakeDate(Order_Date_Year,Order_Date_Month,Order_Date_Day),'YYYY-MMM-DD') as MainDate,

     ... // Rest of your Fields

From Orders.qvd;

LeftJoin(Orders)

Order_Detail:

LOAD

     ID_Order,

     ID_Product,

     Amount as OrderAmount

From Order_Details.qvd;

Concatenate(Orders)

Supplies:

LOAD

     ID_Product,

     ID_Supplier,

     ID_Supply,

     Amount as SupplyAmount,

     UNIT_PRICE,

     Date(

     MakeDate(Supply_Date_Year,Supply_Date_Month,Supply_Date_Day),'YYYY-MMM-DD') as MainDate

From Supplies.qvd;

Following this way you get a table with a single date Field created by Oredr_Dates and Supply Dates and you be able to use in the same table OrderAmount and SupplyAmount.

MasterCalendar:

LOAD Distinct

     MainDate,

     Year(MainDate) as Year,

     Month(MainDate) as Month,

     Day(MainDate) as Day,

     Week(MainDate) as Week,

     MonthName(MainDate) as Period

Resident Orders;

Above Master Calendar has Order Date Fields and Supply Date Fields as single Main Date.

I hope it will be usefull for you.

Best regards.

[PrashantSangle]

Hi,

Try to maintain same format for all dates then rename it to one common name

and then passed that name in Master Calender.

And then use that master calender date for your calculation

Regards

[#9]

Hi,

I've tried do it like that, but it dosen't work.

After raneme date to common name my schema looks like that:

I have error OUT OF VIRTUAL AND/OR LOGICAL MEMORY, allocating  16MB

and QlikView stop working.

What can I do to correct it?

Regards,

Maciej

[#10 · Accepted solution]

Hi,

You have to Join Order Details to Orders to get One Table Orders (With Orders and Order_Details) then concatenate with Supplies (previous you rename Date Fields in Orders and Supplies) finally Create a Master Calendar based on Table Orders (With Order, Order_Details and Supplies). The order of this load is very important... if you have problems give 10 minutes and I'll share you the qvw with this.

Careful in your image I can see you don't Left Join Order_Details and don't concatenate Supplies to Orders just Rename Fields, your script have to be like this:

Orders:

LOAD

     ID_Order,

     ID_Employee,

     ID_Customer,

     Date(

     MakeDate(Order_Date_Year,Order_Date_Month,Order_Date_Day),'YYYY-MMM-DD') as MainDate,

     ... // Rest of your Fields

From Orders.qvd;

LeftJoin(Orders)

Order_Detail:

LOAD

     ID_Order,

     ID_Product,

     Amount as OrderAmount

From Order_Details.qvd;

Concatenate(Orders)

Supplies:

LOAD

     ID_Product,

     ID_Supplier,

     ID_Supply,

     Amount as SupplyAmount,

     UNIT_PRICE,

     Date(

     MakeDate(Supply_Date_Year,Supply_Date_Month,Supply_Date_Day),'YYYY-MMM-DD') as MainDate

From Supplies.qvd;

MasterCalendar:

LOAD Distinct

     MainDate,

     Year(MainDate) as Year,

     Month(MainDate) as Month,

     Day(MainDate) as Day,

     Week(MainDate) as Week,

     MonthName(MainDate) as Period

Resident Orders;

Best regards.