Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
@hi Folks,
I want the subtotals of the pivot table to be shown only if the values in the dimension row is more then one,
if only one row is there then the sub total row should show.
for eg( refer the image attached) i want to show total row if i have both ICATM & USI in second column.
if i have either of the one then the subtotal column should not show.
Message was edited by: swathii gumte Added sample qwv @SunnyTalwar
Message was edited by: swathii gumte Updated sample doc with reduced data
Check this out
I basically looked for expression/s which will have a value in all possible rows and create a variable called vCheck... To be very robust, you might want to use the expression for every single row in vCheck.... I used 15 and 18E because for your sample they covered all the rows between the two expressions....
Sum(TOTAL <DimOverviewCust>
Aggr(If(
$(vICATM_AEFP_m3)
+
$(vUSI_AEFP_m3)
+
$(vICATM_AEFP)
+
$(vUSI_AEFP) <> 0, 1, 0), DimOverviewCust, DimASEGroup)) = 1 and Dimensionality() = 1
What are your dimensions and what is your expression? Are you able to share a sample?
Customer and ICATM/USI columns are my dimension and 15 & 16 columns are my expression
refer attachment
But what is the expressions? Those are just labels
try this -
if(Dimensionality() = 0 or Dimensionality() = 1 or Dimensionality() = 3 or NoOfRows() > 1,
sum(Amount,'') )
Hi Sunny,
I have attached my sample file.
Can you reduce the size and repost?
Preparing examples for Upload - Reduction and Data Scrambling
Hi Sunny,
I have attached the reduced data file, check if this is okay with you.
Looks much better, thank you... check it now
Check this out
I basically looked for expression/s which will have a value in all possible rows and create a variable called vCheck... To be very robust, you might want to use the expression for every single row in vCheck.... I used 15 and 18E because for your sample they covered all the rows between the two expressions....
Sum(TOTAL <DimOverviewCust>
Aggr(If(
$(vICATM_AEFP_m3)
+
$(vUSI_AEFP_m3)
+
$(vICATM_AEFP)
+
$(vUSI_AEFP) <> 0, 1, 0), DimOverviewCust, DimASEGroup)) = 1 and Dimensionality() = 1