topic Re: Handle Null Dimension in Pivot Table in QlikView

Handle Null Dimension in Pivot Table

boonhaw_tan — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I got a pivot table as below where product #bbb contain null time bucket.

I got request to suppress the value of null time bucket for product #bbb by manual assign (60 * 40%) into 1 Week & (60 * 60%) into 2 Months time bucket as below.

Appreciate if anyone can help to provide solution.

Thanks,

Boon Haw

Re: Handle Null Dimension in Pivot Table

sunny_talwar — Sat, 04 Jun 2016 19:10:46 GMT

May be like this:

If(Product = 'bbb', if(TimeBucket = '1 week', Sum(TOTAL <Product> Measure) * 0.40, If(TimeBucket = '2 months', Sum(TOTAL <Product> Measure) * 0.60)), Sum(Measure))

Replace your expressions accordingly based on your actual need

Re: Handle Null Dimension in Pivot Table

sunny_talwar — Sat, 04 Jun 2016 20:50:49 GMT

Here is a sample for you to look at:

Re: Handle Null Dimension in Pivot Table

boonhaw_tan — Sun, 05 Jun 2016 07:43:04 GMT

Hi Sunny,

Thanks a lot for your effort.

From the expression below, the product is set as #bbb, in case there are a lot of product and have no idea which product has null time bucket. How can we find out which product has null time bucket.

If(Product = 'bbb', if(TimeBucket = '1 Week', Sum(TOTAL <Product> Sales) * 0.40, If(TimeBucket = '2 Months', Sum(TOTAL <Product> Sales) * 0.60, 0)), Sum(Sales))

Re: Handle Null Dimension in Pivot Table

sunny_talwar — Sun, 05 Jun 2016 11:15:28 GMT

You can try this:

If(Sum({<TimeBucket = {'*'}>}Value) > 0, Sum(Value),

If(TimeBucket = '1 Week', Sum(TOTAL <Product> Value) * 0.40,

If(TimeBucket = '2 Months', Sum(TOTAL <Product> Value) * 0.60, 0)))

Re: Handle Null Dimension in Pivot Table

boonhaw_tan — Mon, 06 Jun 2016 14:24:07 GMT

Hi Sunny, Thanks a lot for your effort

Re: Handle Null Dimension in Pivot Table

sunny_talwar — Mon, 06 Jun 2016 14:40:00 GMT

No problem at all. Does this give you what you were looking for? If it did, would you be able to close this thread by marking correct and any helpful responses?

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: Handle Null Dimension in Pivot Table

boonhaw_tan — Wed, 29 Jun 2016 12:22:12 GMT

Hi Sunny,

Recently the user request to show partial sums in pivot.

But unfortunately the total value show "-".

Would need your help to check if can show the partial sums value for product bbb.

Re: Handle Null Dimension in Pivot Table

sunny_talwar — Thu, 30 Jun 2016 00:59:54 GMT

Try this:

If(Product = 'bbb' and SecondaryDimensionality() = 1 and Dimensionality() = 1,

If(TimeBucket = '1 Week', Sum(TOTAL <Product> Value) * 0.40,

If(TimeBucket = '2 Months', Sum(TOTAL <Product> Value) * 0.60, 0)), Sum(Value))

Re: Handle Null Dimension in Pivot Table

boonhaw_tan — Thu, 30 Jun 2016 02:20:12 GMT

Thanks a lot Sunny, the solution work perfectly.