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
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mslottje
Creator II
Creator II

Sum two variables with different calendars

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

8 Replies
petter
Partner
Partner

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
Master II
Master II

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?

mslottje
Creator II
Creator II
Author

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?:)

mslottje
Creator II
Creator II
Author

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
Master II
Master II

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

Can you share your expression?

mslottje
Creator II
Creator II
Author

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
Master II
Master II

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?


mslottje
Creator II
Creator II
Author

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