google sheetsgoogle-apps-scriptpivot table
Given the general difficulty of doing much with google pivot table, I expect the answer to be "No".
In the snippet below I would like to keep lines with the totals in green shading and not see the lines with yellow shading.
Clicking the – signs collapses the detail leaving only the total.
It also would require a manual step to to go through and check each time the inventory changes.
In effect I want:
"if (number of details lines = 1) then (Don't show total)
I don't, except I suspect you are out of luck for the time being at least. However, even in Excel I have often found it as, or even more, useful to perform such grouping in the base data rather than in the pivot table. For me the summary provided by the table is often a prelude to drilling down for further detail and rather than end up then with many different extracts I find it much easier to filter the source data, which I have already formatted to suit me.
On that basis a hack of sorts may serve for you. Assuming data as in D:E in the image, I added three columns with formulae:
=year(D2) =month(D2)=weeknum(D2)To reduce the need for recalculation I usually then convert the results to values.
ColumnsA:C can then be included in the pivot table and, unlike Grouping, the components of the date can be split between rows and columns in the pivot table:
Although you mention only A2 in sheet March I'm guessing you may want equivalent formatting throughout ColumnA, so start by selecting ColumnA then:
Format, Conditional formatting..., Custom formula is:
=match(A1,indirect("Roster"&"!D:D"),0)>0
select yellow (Range: should be A:A) and Save rules.
For each colour you need a separate rule (unless one colour is acceptable by default, so standard formatting applied for that) hence for red for example + Add another rule of:
=match(A1,indirect("Roster"&"!E:E"),0)>0
and repeat for blue (B:B in place of D:D in the first formula) and green (C:C).
Best Answer
Click the [-] button on the row above the subtotal that you want to hide.