need help on Ad-hoc report

[qv_testing]

Hi Community,

I have done simple Ad-hoc report,

if i make selections Dimension = Year and Measure= A,B,C and Total

here total working fine 1550,  total calculation A+B+C.

MY requirement, if i make single selection Measure= A and Total,  i have to show my total, my total not showing because,

i have written my expression Column(1)+Column(2)+Column(3),

and if i'm make selections A, B and Total, i need to show based on selection Total.

same as A, C and Total

How to write expression total ?

Thanks in Advance...

[tresesco]

Use below expression for Total column:

=RangeSum(column(1),Column(2),Column(3))

[PrashantSangle]

Try below expression for Total

=

if(SubStringCount('|' & Concat(distinct Measure, '|') & '|', '|0 - 30|')=1,Column(1),0)+

if(SubStringCount('|' & Concat(distinct Measure, '|') & '|', '|31 - 60|')=1,Column(2),0)+

if(SubStringCount('|' & Concat(distinct Measure, '|') & '|', '|61 - 90|')=1,Column(3),0)

Regards,

[qv_testing]

this expression multiplying twice.

[PrashantSangle]

did you try my suggestion???

Regards,

[qv_testing]

this expression already i had tried, problem is if i make selection order only working fine.

suppose, if i make selection 31-60, 61-90 and total. Total not showing.

[tresesco · Accepted solution]

Yes, you are right, and this is because the Total column itself is getting added in such scenarios. I have got a more dynamic (a bit tricky too) expression. Try :

RangeSum($(=mid(Concat(',Column(' &ValueLoop(1,$(=Count(Measure)-1))&')' ),2)))

[stabben23]

Hi, instead of column(), use RangeSum([0-30],[31-60],[61-90])

[PrashantSangle]

can we do it like simply

(your_expression)/2

Regards,

[tresesco]

Perhaps not. Because of two reasons:

• The Total calculation would get affected again by /2 ; recursive effect
• When all measures are selected, no division is expected, hence /2 would give wrong result

[PrashantSangle]

Thanks...