Qlik Pivot - Distribution Tail

[karandeep]

Hi there, 

I am working on producing a distribution tail showing how many days items take to be delivered across Departments A, B, C and Others. At the moment, the Qlik Pivot Table leaves some cells blank. Instead of blanks or hyphens, I need an expression that carries forward the previous day’s value so the distribution tail remains continuous. Any help would be appreciated, Many thanks. 

[priscilarubim]

Hi,

I reproduced your data in Qlik to understand it better.

Looking at the initial script:

We get the following result:

Row: Department

Column: DayLabel

Measure (%):

Num(

    RangeSum(Before(Count(ItemID), 0, ColumnNo()))

    /

    Count(TOTAL <Department> ItemID)

, '0%')

Then I added this debug measure to understand what's going on:

=ColumnNo() & ' | ' & Count(ItemID) & ' | ' & RangeSum(Before(Count(ItemID), 0, ColumnNo()))

What each part shows:

• ColumnNo() — the column number where the cell is being rendered (1 for Day 1, 2 for Day 2... up to 7).
• Count(ItemID) — how many items were delivered in that cell.
• RangeSum(Before(...)) — what the accumulation expression is producing there.

When a combination of Department × DeliveryDay doesn't exist in the source data (for example, nobody in Department A delivered anything on Day 2), Qlik simply doesn't render that cell in the pivot. The - you see is just a visual placeholder — it doesn't exist as an actual cell. And if the cell doesn't exist, Before() can't iterate over it.

Notice that in the cell that visually sits under "Day 5", ColumnNo() returns 2, not 5. And in the one under "Day 6", it returns 3. Qlik is numbering the columns by visible position within the row (1st cell with data, 2nd, 3rd...), not by the absolute column position in the pivot.

So I changed the script a bit:

The idea was to create artificial rows so that Qlik has every Department × Day combination.

Before:

A | Item 1 | Day 1

A | Item 2 | Day 5

A | Item 3 | Day 6

Now, even when no item was delivered on a given day, the combination still exists. That's why the pivot stops treating the cell as nonexistent:

A | Day 1

A | Day 2

A | Day 3

A | Day 4

A | Day 5

A | Day 6

A | Day 7

And we get this result:

That's the path that worked for me, but I'm sure there are other ways to get there. Curious to hear how others would approach this.

[karandeep]

Thank you @priscilarubim  for looking into this, I have hundred thousands records that i am working on. Is it possible to have an expression that could populate the required distribution tail, Many thanks.

[priscilarubim]

@karandeep   unfortunately, I don't think there's a measure-only solution. If a Department × DeliveryDay combination doesn't exist in the data, Qlik doesn't render that cell... so no expression can run there.

That's the best solution I could come up with. If anyone in the community has solved this differently, I'd love to know how too.