Qlik Community

Qlik Sense App Development

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

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
zhujason858_cha
New Contributor II

How to calculate months with missing records

I would like to calculate the cumulative monthly quantity over the course of the year. So when I try using the below formula to create my table i get the following output, which is not quite what I wanted:

Data (example): Note that not every Year-Month has a recorded quantity for given product

ProductYear-MonthQty
BCG2016-Jan1,300,000
BCG2016-Mar3,476,000
bOPV........

Formula

RangeSum(Above(TOTAL Sum([Qty]), 0, RowNo(TOTAL)))

Output

Help Qlik Cum.png

Problems:

1) I would like the cumulative counter to set back to 0 in the beginning of every year

2) For months without records (the grey), I would like it so that 0 is added to the cumulative sum. So for example BCG in 2016-Jan is 1,300,000, and I would like BCG in 2016-Feb to be 1,300,000 (so 1,300,000 + 0)

3) There is no row for 2017-Jan because there are no records for any of the products that Year-Month. But I would like all the columns for that Year-Month to be 0.

Any help would be greatly appreciated!!

Tags (1)
1 Solution

Accepted Solutions

Re: How to calculate months with missing records

I suggest using The As-Of Table

2 Replies

Re: How to calculate months with missing records

I suggest using The As-Of Table

zhujason858_cha
New Contributor II

Re: How to calculate months with missing records

that was super helpful sunny, an as-of table is exactly what i needed here

Community Browser