Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

Preventing totals for text expresions on Pivot tables

have a pivot table which has products with their planned source and destination with the associated costs....

I have a 2nd table which has other ' sourcing options' with their associated costs. I calculate the sources that would be the least expensive ( Source 1, Source 2, Source 3) using an expression but the end result is a name of a location (i.e florida, colorado, california, etc) but in my pivot table when everything is aggregated up to the product level, it shows the "Total" for the  Source 1 as Florida, which really makes no sense.

I want it to show the  Source 1, 2, 3 only when I've expanded from Product to Destination.

Any idea on how to go about this?

20 Replies
Employee
Employee

Re: Preventing totals for text expresions on Pivot tables

Might need to see a sample for this one.

Not applicable

Re: Re: Preventing totals for text expresions on Pivot tables

I'm trying to create a sample for this. In my sample where I created the sample data, it is working exactly how I want it to...

In my actual application, where you see 'option1,2,3' you would see a total of 1B, 2B, 3B when you have everything collapsed looking at totals for each week...

do you think in my actual application, it is thinking the source locations are numeric values?

Employee
Employee

Re: Re: Preventing totals for text expresions on Pivot tables

So the sample works the way you want , but there is an issue  when everything is collapsed (that is not in the actual QVW)

.

But the actual QVW does not work.

Does your sample tackle the solution differently than the actual qvw and you now want me to help resolve the collapsing issue ? 

Not 100% sure what issue to solve ?

Not applicable

Re: Preventing totals for text expresions on Pivot tables

My actual QVW is designed the same way as my sample QVW. My sample QVW works like how I want it to.

However, my actual QVW is giving me totals for text expressions when the pivot table is collapsed to the week level. Do you know what may cause this to happen?

Nothing should show on the 'other options' columns until you expanded the chart to the 'source' field like in the sample QVW. In my actual QVW, it is giving me the 'other options' when I have it collapsed to the week level.

Employee
Employee

Re: Preventing totals for text expresions on Pivot tables

I'm not sure.

Not applicable

Re: Preventing totals for text expresions on Pivot tables

Has anyone else experienced this issue?

kimrormark
Contributor

Re: Preventing totals for text expresions on Pivot tables

You need to provide an example qvw WITH the problem..

Not applicable

Re: Re: Preventing totals for text expresions on Pivot tables

I finally have an example!! Please help as I'm really having a lot of trouble with this.

Here we go - please look at the attached QV. When everything is collapsed o the 'calendar_week' level, there is a Total that I do not want to see. I only want to see those options if I expand the pivot table all the way to the 'Destination' column.

Please help!! any input is appreciated.

kimrormark
Contributor

Re: Re: Re: Preventing totals for text expresions on Pivot tables

Hi,

On the Presentation tab for the pivot, you can select/deselect "Partial Sums" for all dimensions. If you deselect all, except for dimension "Destination", you will only see Totals when "Destination" is visible.

If you show "Partial Sums" for "Shipments", you will have one Total for each Shipment value - which is not what you want I guess?

Please show the attached qvw where I did these changes on the Presentation tab.