topic Re: Grand Total Columns for expressions in QlikView

Grand Total Columns for expressions

ashokunbi — Fri, 09 Sep 2016 17:09:41 GMT

I have two expressions, one for sum and one for avg that is displayed in columns under each selected week from list table (number of columns change depending on the number of weeks selected. I would like to add grand total columns for the sum and avg expression as the last two columns. My problem is that when I created the grand total expression it's being displayed for each week with wrong data of course. Any help will be appreciated.

Re: Grand Total Columns for expressions

neelamsaroha157 — Fri, 09 Sep 2016 17:35:11 GMT

You can create your expression as

If(columnNo() = 0, then 'Avg Expression' , 'Your_Original_Expression')

This will give your Total as Avg and wont display for each week.

Re: Grand Total Columns for expressions

Anil_Babu_Samineni — Fri, 09 Sep 2016 17:35:55 GMT

Like this

Sum( total Sum + avg)

Re: Grand Total Columns for expressions

saimahasan — Fri, 09 Sep 2016 17:44:50 GMT

Since its pivot, it should take the grand total when selecting the partial totals option.

I dont know whether i am taking you correct or not. but i think you are asking for something like this...

Re: Grand Total Columns for expressions

ashokunbi — Fri, 23 Sep 2016 16:28:10 GMT

Thanks. Sorry for the late response. Been away for a while. This displayed for each week as well with the same result I already had.

Re: Grand Total Columns for expressions

ashokunbi — Fri, 23 Sep 2016 16:36:19 GMT

Thanks. Sorry for late response, been away for a while. You are correct but number of weeks is not static. Number weeks selected depends on the user but expression will calculate for number of weeks selected. See example below.

			Week Number Of Year	W29 2016	W29 2016	W33 2016	W33 2016	W34 2016	W34 2016	Selected Wks	Selected Wks
Group Name	Name	Grade	Technology	Units	ASP	Units	ASP	Units	ASP	Total Units	Avg ASP
ABC	ABC-1	C	GSM	-	-	1	$5.00	-	-	?	?
ABC	ABC-1	C	GSM	8	$4.88	7	$5.00	-	-	?	?
ABC	ABC-1	D	GSM	15	$4.35	-	-	-	-	?	?
ABC	ABC-2	C	GSM	3	$4.88	1	$5.00	-	-	?	?
ABC	ABC-2	C	GSM	3	$4.88	-	-	-	-	?	?

Re: Grand Total Columns for expressions

ashokunbi — Fri, 23 Sep 2016 18:14:34 GMT

Not sure why this is marked as correct answer because it is not. Please remove correct designation as it would prevent others from actually answering the questions.

Re: Grand Total Columns for expressions

sunny_talwar — Fri, 23 Sep 2016 22:30:18 GMT

You should be able to remove the correct response yourself. Check here:

Qlik Community Tip: Marking Replies as Correct or Helpful Qlik Community Tip: Marking Replies as Correct or Helpful

The above link shows how to unmark the correct answer as well

Re: Grand Total Columns for expressions

sunny_talwar — Fri, 23 Sep 2016 22:34:18 GMT

I think what saimahasan‌ provided is what you would need. Go to the presentation tab and try selecting enabling 'Show Partial Sum' for your Week Number of Year dimension and no matter how many weeks you have, the end result will be the just one column for each of your expression. Give it a try as there is no cost of trying in QlikView.

Best,

Sunny

Re: Grand Total Columns for expressions

neelamsaroha157 — Mon, 26 Sep 2016 13:25:34 GMT

Hey Ade,

Its been marked correct by you.

Re: Grand Total Columns for expressions

ashokunbi — Mon, 26 Sep 2016 14:27:35 GMT

Thanks. I actually went a different route by having the grand total before the weeks. Since the number of weeks displayed could be quite long depending on the user, seeing the grand totals without having to scroll all the way right was more prudent. Since it's a pivot table, a user can then un-collapse the grand total to see selected weeks if desired.