topic Re: need help on Ad-hoc report in QlikView

need help on Ad-hoc report

Nagaraju_KCS — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: need help on Ad-hoc report

kaushiknsolanki — Wed, 29 Nov 2017 14:39:08 GMT

Hi,

If this is your real time scenario then try below expression for total.

=Sum({<Company={"$(=concat(distinct Measure,'","'))"}>}Sales)

Regards,

Kaushik Solanki

Re: need help on Ad-hoc report

kaushiknsolanki — Wed, 29 Nov 2017 14:57:03 GMT

no it wont.

tell us your real scenario with real values.

Regards,

Kaushik Solanki

Re: need help on Ad-hoc report

Nagaraju_KCS — Wed, 29 Nov 2017 15:52:08 GMT

Please have a look my real example....

PFA, !

Re: need help on Ad-hoc report

Nagaraju_KCS — Thu, 30 Nov 2017 06:53:14 GMT

Please can you help on this...

Re: need help on Ad-hoc report

Anil_Babu_Samineni — Thu, 30 Nov 2017 06:56:58 GMT

You can use Full expression rather Column(). check and let us know. Off course, In Conditional enable you need to manipulate same thing

Re: need help on Ad-hoc report

Nagaraju_KCS — Thu, 30 Nov 2017 07:07:24 GMT

No, it won't work because it will return whole sum.

i want to change based on Measure selection.

Re: need help on Ad-hoc report

Anil_Babu_Samineni — Thu, 30 Nov 2017 07:13:19 GMT

I need to look attachment, Will help you for sure once i get access the app

Re: need help on Ad-hoc report

kaushiknsolanki — Thu, 30 Nov 2017 07:52:26 GMT

Hi Can you tell us your expected output.

Regards,

Kaushik Solanki

Re: need help on Ad-hoc report

Nagaraju_KCS — Thu, 30 Nov 2017 08:44:14 GMT

If i make all Measure selections, my total coming proper.

By My scenario, if i make any selection

0-30 and Total,

0-30, 31-90 and Total

i want to total as well...

PFA,

Re: need help on Ad-hoc report

tresB — Thu, 30 Nov 2017 08:53:47 GMT

Use below expression for Total column:

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

Re: need help on Ad-hoc report

PrashantSangle — Thu, 30 Nov 2017 08:56:01 GMT

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,

Re: need help on Ad-hoc report

Nagaraju_KCS — Thu, 30 Nov 2017 09:11:15 GMT

this expression multiplying twice.

Re: need help on Ad-hoc report

PrashantSangle — Thu, 30 Nov 2017 09:14:30 GMT

did you try my suggestion???

Regards,

Re: need help on Ad-hoc report

Nagaraju_KCS — Thu, 30 Nov 2017 09:14:41 GMT

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.

Re: need help on Ad-hoc report

tresB — Thu, 30 Nov 2017 09:25:41 GMT

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

Re: need help on Ad-hoc report

stabben23 — Thu, 30 Nov 2017 09:28:31 GMT

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

Re: need help on Ad-hoc report

PrashantSangle — Thu, 30 Nov 2017 09:32:33 GMT

can we do it like simply

(your_expression)/2

Regards,

Re: need help on Ad-hoc report

tresB — Thu, 30 Nov 2017 09:47:14 GMT

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

Re: need help on Ad-hoc report

PrashantSangle — Thu, 30 Nov 2017 09:51:49 GMT

Thanks...