topic Re: Pivot table totals after every two rows? in QlikView

Pivot table totals after every two rows?

vishsaggi — Fri, 24 Feb 2017 16:37:25 GMT

Hello All,

Can you please suggest? I need the subtotals in pivot table.

Attached the file.

Re: Pivot table totals after every two rows?

sunny_talwar — Fri, 24 Feb 2017 16:47:21 GMT

Every two rows? Can you share a qvw where you might already have this data loaded?

Re: Pivot table totals after every two rows?

vishsaggi — Fri, 24 Feb 2017 16:52:03 GMT

Thanks sunny, sure will do that. It is a big file will reduce and upload in a while.

Re: Pivot table totals after every two rows?

vishsaggi — Fri, 24 Feb 2017 18:06:33 GMT

Hi Sunny,

I need the totals for every two weekstart dates. As mentioned in the excel. Attached QVW file.

Many thanks,

Re: Pivot table totals after every two rows?

vishsaggi — Fri, 24 Feb 2017 23:49:11 GMT

Hello Sunny,

When you get time, can you suggest some on this please....

Many thanks,
Vish.

Re: Pivot table totals after every two rows?

sunny_talwar — Sat, 25 Feb 2017 17:39:09 GMT

I will look at this when I get time today and tomorrow. Sorry, did not come back to you yesterday.

Best,

Sunny

Re: Pivot table totals after every two rows?

Anonymous — Sat, 25 Feb 2017 18:46:33 GMT

Am using personal edition so unable to view your qvw.

Creating a column like

etc and using it in the dimension or using a calculated dimesnsion like above in the pivot table and checking the subtotals for Weekstart will do the job.

Try using a autogenerate col for all the records with Rowno() as even and same for Odd. When concatenated, will give the required column.

Hope it helps

Re: Pivot table totals after every two rows?

vishsaggi — Mon, 27 Feb 2017 17:43:50 GMT

No Problem Sunny. I just tried this but i am not sure if this can be used in the calendar script, but this is what i am looking for? Any suggestions?

= IF(Even(Aggr(RowNo(Total), DayOfMonth)), Aggr(RowNo(Total), DayOfMonth), Aggr(RowNo(Total), DayOfMonth)+1)

My Expected output:Which is coming correctly when the above expression is used in the dimensions. How can i just hide the rows column but still get the grand totals after every two rows?

Re: Pivot table totals after every two rows?

sunny_talwar — Mon, 27 Feb 2017 17:50:53 GMT

Which row are you looking to hide?

Re: Pivot table totals after every two rows?

vishsaggi — Mon, 27 Feb 2017 17:55:16 GMT

The column named "Rows".

I did reduced the size of that column and Custom Format Cells text color to White but, just wondering if any other way to hide that column?

Re: Pivot table totals after every two rows?

sunny_talwar — Mon, 27 Feb 2017 17:58:18 GMT

May be use the Macro to make it 0 width?

Re: Pivot table totals after every two rows?

sunny_talwar — Mon, 27 Feb 2017 17:59:21 GMT

Something like this:

SUB Squeeze

CALL HideColumn("CH01", 1)

END SUB

PRIVATE SUB HideColumn(ch, n)

SET ch = ActiveDocument.GetSheetObject(ch)

ch.SetPixWidth (n-1), 2

END SUB

Re: Pivot table totals after every two rows?

vishsaggi — Mon, 27 Feb 2017 18:15:57 GMT

This helped. Thanks very much. Probably i will go with my expression which is pretty suffice my requirement.
Thanks a lot Sunny for your time. Appreciate your time.