Sort a Pivot Table with blank fields as 0

[#1]

I have a pivot table with 4 dimensions (customer, etc, etc) and two expressions, sales this year and sales last year.

sales this year = Sum(SALES)

sales last year takes the user date selections and collects the data from 1 year ago

Sum({<Year = {"$(=GetFieldSelections("Year")-1)"} >} SALES) 

There are some customers that do not have sales this year, but do have sales last year.

My problem is when I try to sort the pivot table by sales this year (Sum(SALES), the customers that have no data in sales this year are automatically placed at the top of the list. They display a 0 value, but I think because the actual value in the cell is blank, Qlikview is not sorting them as 0 values.

Does anyone know how I can fix this?

[#2]

Rick

You could try amending your expression for sales this year to deal with null values and convert to zero, something like:

=IF(ISNULL(Sum(Sales)),0,Sum(Sales))

Hope it helps,

[#3]

Thanks Nigel. I played with your idea. I had to remove the Null values in the Sales Last Year field, which worked.

Sales This Year = Sum(SALES)




Sales Last Year = If (ISNULL([Sales This Year]), NULL(),IF([Sales This Year]=0,NULL(), Sum({<Year = {"$(=GetFieldSelections("Year")-1)"} >} SALES)))

This is not ideal because it excludes Customers with sales last year but no sales this year. I can live with this because I only need the top customers.

I think the issue was the order in which the expressions were being calculated/populated - [SalesThis Year] first and [Sales Last Year] second, so whatever I seemed to specify in [Sales This Year] to exclude Null values had no effect, because they appeared when [Sales Last Year] was calculated.

Not the cleanest of solutions, but it works for the moment!!!

[#4]

You should probably use indirect set analysis (use Help for syntax or one of the Set Analysis qvws)

Sample questions:

- select customers with sales from last year where they have sales this year

- select customers with sales from last year where they have no sales this year

- select customers with sales from this year where they had sales this year

- select customers with sales from this year where they had no sales this year

[#5]

Thanks Mark.

I went with the original solution as it gave the correct data. Your solution looks good, but I am working on other things now, so I will get back to it when I clean up this part of the project.

Thanks for your help.

[#6 · Accepted solution]

For ones that are looking for the solution:

I had a similar problem. The solution is to sort by expression and in this expression use rank function, for ex.

rank(count(id)) and sort it ascending. This will solve the problem . It worked for me.