Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
qv_testing
Specialist II
Specialist II

need help on Ad-hoc report

Hi Community,

I have done simple Ad-hoc report,

Capture.PNG

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

1.PNG

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),

Capture.PNG

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...

22 Replies
tresesco
MVP
MVP

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,

Great dreamer's dreams never fulfilled, they are always transcended.
qv_testing
Specialist II
Specialist II
Author

this expression multiplying twice.

PrashantSangle

did you try my suggestion???

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
qv_testing
Specialist II
Specialist II
Author

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.

Capture.PNG

tresesco
MVP
MVP

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)))

Capture.JPG

stabben23
Master
Master

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

PrashantSangle

can we do it like simply

(your_expression)/2

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
tresesco
MVP
MVP

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...

Great dreamer's dreams never fulfilled, they are always transcended.