rangesum above in a pivot with gaps in the data

suneethi — Sat, 16 Nov 2024 17:33:22 GMT

Hi,

I am trying to do a 3 month range sum in a pivot table using rangesum before -

(rangesum(Before(total sum({<[PO Month Value]=>}[Total OT]),0,3)))

There are gaps in the data. Is there anyway, in case of a null measure, I can have the rangesum of the previous 2 measures and put in the place of null and also the next one to consider the 2 months where the measures were not null?

For example in the screen shot - first row, Jun-20 is no value. I would like to have Apr-20+May-20 (+jun-20 which is null) to be there for Jun-20, and then have may-20+jul-20 (since Jun-20 is null) for jul-20 values?

So basically put 0s for the gaps (jun-20) without changing in the underlying data tables.

Thanks!!

Re: rangesum above in a pivot with gaps in the data

Ksrinivasan — Fri, 29 Jan 2021 03:14:44 GMT

hi,

Use Alt() like below

=Alt(Expression, 'Value If expression returns null')

Example:

=Alt(100/0, '0')

or try this link for more

https://community.qlik.com/t5/Qlik-Sense-App-Development/Convert-Null-to-zero-in-Aggr-when-all-the-rows-has-been-excluded/td-p/128086

Hope this helps you.

ksrinivasan

topic Re: rangesum above in a pivot with gaps in the data in App Development

rangesum above in a pivot with gaps in the data

Re: rangesum above in a pivot with gaps in the data