0 Replies Latest reply: Sep 16, 2015 7:51 AM by Ferdi Kruger RSS

    Qlik sense: show start and end totals per month

      Hi

       

      I am trying to create a table which shows 4 columns by MonthYear (the dimension):

      - Start value

      - New Customers for the month

      - Customers removed last month

      - Movement in Other Customers

      - End Value

       

      I am trying to do this using set analysis, however only the end value and New entries is simple for me.

       

      for example I've tried the following to get the Star value (which qould be equal to month end value of last month):

      Sum({<
      MonthYear={"$(=MonthEnd(AddMonths((MonthYear), -1)))"}>}

      [Inventory])

       

      I am not an IT person, but a business user trying to figure this out.

       

      I've managed the New entries by creating a flag in my script if it's the 1st date a record appears:

      Sum({1<

      Flag_First_Date = {1}

      >}

      [Inventory])

       

      Similarly I've created a flag for the last time an entry appears, but this is shown in the previous month.

       

      Example data:

      MonthYear   InventoryBalance  Customer  Flag_First_Date  Flag_Last_Date

      03/2015         250                            b                    1                         0

      04/2015        100                             a                    1                         0

      04/2015        50                               b                    0                         1

      04/2015        80                               c                    1                         0

      05/2015        110                             a                    0                         0

      05/2015        130                             c                    0                         1

      06/2015        150                             a                    0                         1

       

      Output I'm hoping to see:

      MonthYear    Start_Value            New          Removed                 Other_Movements     End_Value

      04/2015             250                      180                  0                             -200                            230

      05/2015             230                         0                -50                              60                              240                          

      06/2015             240                         0                -130                            40                              150

       

      Hoping this makes sense and someone can assist?

       

      Thanks