Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
These two documents are essential reading so you could apply best-practices when dealing with dates:
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
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?
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?:)
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!
What fields you've created, what expression you writing in variable?
Can you share your expression?
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:
(SUM({<InvoiceYear = {"2017"}>}[Nett Amount EUR])+Sum({<InvoiceYear = {"2017"}>}Orderportfolio EUR])
SUM({<InvoiceYear = {"2016"}, InvoiceMonth = {"jan"}>}[Nett Amount EUR])
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!
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?
Unfortunately it's a bit difficult to post the sample data, but the solution for this problem is combining the different calendars into one.