Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

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