Can we customize totals in a straight table?

[#1]

Hi,

I need to know if the totals for expressions in a straight table can be customized. I have a straight table that shows cost and sales for every month. The users would also like to see months where there are no sales or no costs based on their selections. Hence I changed the expressions to check if the fiscal month is null as follows -

"IF(IsNULL(Fiscal_Month_Year),0,Sum(RandA.RACost))". This allowed me to show a zero for those months when there was no cost and making a similar change allowed me to show zero for sales when there was no sales. On unchecking the option "Suppress Missing" in the presentation tab, i am now able to show zero for months where there are no sales.

However, my total row for these two expressions showed zero and hence I changed the total mode to show the sum of rows.

The user then asked for a % of Sales value to also be displayed. I created this as a ratio of Cost to Sales where the expressions Cost and Sales had been defined earlier as defined above. However, the total mode for this expression does not yiled the right results. When i choose expression total, it shows 0 probably because there are null values. If i choose the mode as sum of rows, it again yields incorrect results as it is aggregating %of sales values across all months. The other functions avaialble there also do not make sense for what i am trying to achieve. I would like to have total compute the same as the expression (i.e total cost/total sales).

The question now is - Can I customize how I calculate my totals?

If there is any alternate way of achieving this, that will also be good to know.

Thanks,

[#2 · Accepted solution]

First thing you must do is change the sum position

SUM(IF(IsNULL(Fiscal_Month_Year),0,RandA.RACost))

to show % you can use a expr.:

SUM(IF(IsNULL(Fiscal_Month_Year),0,RandA.RACost))
/
SUM(TOTAL IF(IsNULL(Fiscal_Month_Year),0,RandA.RACost))

[#3]

Thanks Erico. That worked. In hindsight, I recognize it was pretty foolish of me to include the sum inside the if condition.

[#4]

Hi All-

I have a similar problem. However, I'm not too good at using QlikView so I definitely need some help from someone who can help answer my question quickly!

My issue is that I have a straight table of financial data for the past 5 years and a number of different companies. When I make the straight table, the "Total mode" will not let me customize what becomes the total mode for certain dimensions of my table. I have six dimensions that are found by calculating two or more of the other dimensions together so what I want is the total mode to be not a sum of all the values in the column but rather a sum of all the values in column "A" divided by the sum of all the values in column "B" and what ever that is needs to be the total mode. So it's not a sum and not an average, but rather applying the calculation I used to get the vaules in that column to the "total mode". If you can answer this please let me know ASAP!!

Also, I don't know where to write the expression if there is one to fix this problem because I dont want to mess up the calculations for the columns as a whole.

Let me know if any of this is unclear and I will try and clarify.

Thanks,

Allie