Sum two variables with different calendars

[Anonymous]

Hi guys,

I have the following two measures:

     - Sales as Nett Amount

     - Outstanding orders which are confirmend and waiting to be shipped as Orderportfolio

The Nett Amount is based on the InvoiceCalender, which is the date when an order has been invoiced.

The Orderportfolio is based on the ShipmentCalender, which is the date when the order is planned to ship.

*Note: both calendars are based on the same MasterCalendar.

I want the sum of these variables, so I'm able to figure out what the total sales of a certain month will be.

I tried to sum these two measures in my loading script in Qlik Sense. With this calculation, I found the right Nett Amount, but the Orderportfolio has been measured by the date the order has been added to our system (which is wrong).

Which function/expression should I use to sum these two variables, sorted by the two calendars?

And should I do this in my loading script or in the bar chart?

Thanks in advance!:)

Mike

[petter]

These two documents are essential reading so you could apply best-practices when dealing with dates:

Canonical Date‌

Why You sometimes should Load a Master Table several times

There is also a document that points to even more resources on the subject including the two above:

How to use - Master-Calendar and Date-Values

The SHORT ANSWER is probably:

     Make two separate Master Calendars that point to each date

[MK9885]

Or you can create 2 different date fields?

One for InvoiceCalender and other for ShipmentCalender, while using DateID to join it with your Master Calendar and in Master Calendar create Effective Date (all Calendar Dates).

Date(InvoiceCalender,'MM/DD/YYYY') as InvoiceDate

Date(ShipmentCalender,'MM/DD/YYYY') as ShipmentDate

And in front end use different date functions you created for different variable?

[Anonymous]

Yeah that's what I've done!:)

I have an InvoiceDate and a ShipmentDate.

I wasn't able to figure out how to use these different dates in the front end when I was trying to show them in a bar chart. What was your idea to solve this?:)

[Anonymous]

Thank you for your response Petter!

I will read the blogs that you've recommended.

Regarding your short answer, that's exactly what I did.

I have an InvoiceCalendar, an OrderCalendar and a ShipmentCalendar.

My current problem is that I don't know how to sum the a variable from the InvoiceCalendar with a variable from the ShipmentCalender. I tried the following simple calculation in my loading script:

[Orderportfolio] + [Nett Amount] as [Invoiced+Portfolio]

My problem is that I get the proper Nett Amount per month, but the Orderportfolio is also displayed by InvoiceMonth instead of the ShipmentMonth.. This is the problem I'm trying to solve.

Perhaps I didn't described that very clear In the mean time I will read the blogs!

[MK9885]

What fields you've created, what expression you writing in variable?

Can you share your expression?

[Anonymous]

First, I tried the following simple calculation in my loading script:

[Orderportfolio] + [Nett Amount EUR] as [Invoiced+Portfolio]

I tested this variable in a bar chart and that didn't worked out as I hoped, because both variabels were measured by the InvoiceCalendar.

The different calendars are based on the MasterCalender and could be found in the attached .txt file:

Second, I tried to do this with expressions in the bar chart that I was making.

My goal is to make a bar chart where there is one Dimension (InvoiceMonth) and 2 measures:

• Invoiced 2017 + OrderPortfolio as

          (SUM({<InvoiceYear = {"2017"}>}[Nett Amount EUR])+Sum({<InvoiceYear = {"2017"}>}Orderportfolio EUR])

• Invoiced 2016 as

        SUM({<InvoiceYear = {"2016"}, InvoiceMonth = {"jan"}>}[Nett Amount EUR])

• Additional will be targets

The blue variables should be measured by InvoiceMonth. The red measure should be measured by ShipmentMonth.

Next to that, I'll have to change the hardcoded years and month in to today() and today()-1 expressions.

So, my current problem is that I don't know how to sum a variable from the InvoiceCalendar with a variable from the ShipmentCalender.

I hope I've made my describing clear enough. If not, please let me know!:)

Your help is much appreciated!

[MK9885]

The script which you've written has TempDate in common.

How are you suppose to get different dates from a single date?

floor(date(TempDate,'DD-MM-YYYY')) AS %key_InvoiceDate

floor(date(TempDate,'DD-MM-YYYY')) AS %key_ShipmentDate

Those both fields will return same dates.

Do you have date fields coming from your data source/fact?

If yes then use that instead of Temp date for each table.

Ex:

floor(date(InvoiceDate,'DD-MM-YYYY')) AS %key_InvoiceDate

floor(date(ShipmentDate,'DD-MM-YYYY')) AS %key_ShipmentDate

Now you'll have different date fields.

Is there a way you can post your sample data?

[Anonymous]

Unfortunately it's a bit difficult to post the sample data, but the solution for this problem is combining the different calendars into one.