Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
boonhaw_tan
Creator
Creator

Handle Null Dimension in Pivot Table

Hi All,

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

ScreenHunter_230 Jun. 04 23.13.jpg

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.

ScreenHunter_232 Jun. 04 23.18.jpg

Appreciate if anyone can help to provide solution.

Thanks,

Boon Haw

9 Replies
sunny_talwar

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

sunny_talwar

Here is a sample for you to look at:

Capture.PNG

boonhaw_tan
Creator
Creator
Author

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))

sunny_talwar

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)))


Capture.PNG

boonhaw_tan
Creator
Creator
Author

Hi Sunny, Thanks a lot for your effort

sunny_talwar

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

boonhaw_tan
Creator
Creator
Author

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.

ScreenHunter_232 Jun. 29 20.17.jpg

sunny_talwar

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))

Capture.PNG

boonhaw_tan
Creator
Creator
Author

Thanks a lot Sunny, the solution work perfectly.