Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vishsaggi
Champion III
Champion III

Pivot table totals after every two rows?

Hello All,

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

Attached the file.

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III
Author

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?

Capture.PNG

View solution in original post

12 Replies
sunny_talwar

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

vishsaggi
Champion III
Champion III
Author

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

vishsaggi
Champion III
Champion III
Author

Hi Sunny,

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

Many thanks,

V.

vishsaggi
Champion III
Champion III
Author

Hello Sunny,

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

Many thanks,
Vish.

sunny_talwar

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

Best,

Sunny

Anonymous
Not applicable

Am using personal edition so unable to view your qvw.

Creating a column like

1

1

2

2

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

vishsaggi
Champion III
Champion III
Author

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?

Capture.PNG

sunny_talwar

Which row are you looking to hide?

vishsaggi
Champion III
Champion III
Author

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?