8 Replies Latest reply: Jan 30, 2017 3:04 AM by Mike Slottje RSS

    Sum two variables with different calendars

    Mike Slottje

      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

        • Re: Sum two variables with different calendars
          Petter Skjolden

          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

            • Re: Sum two variables with different calendars
              Mike Slottje

              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!

            • Re: Sum two variables with different calendars
              Aehman K

              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?

                • Re: Sum two variables with different calendars
                  Mike Slottje

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

                    • Re: Sum two variables with different calendars
                      Aehman K

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

                      Can you share your expression?

                        • Re: Sum two variables with different calendars
                          Mike Slottje

                          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!



                            • Re: Sum two variables with different calendars
                              Aehman K

                              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?