topic Re: Expression help in QlikView

Expression help

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

I have attached the sample excel and the QVW I am trying to work out...

I need some help with the expression calculation...any help is appreciated...I have my request in QVW.

Thanks much.

Re: Expression help

m_woolf — Mon, 24 Jul 2017 18:54:42 GMT

See the attached qvw. !

Re: Expression help

md_qlikview — Mon, 24 Jul 2017 19:00:25 GMT

Try below Expression

sum(TOTAL [Adj Net Size - SF])-sum(TOTAL {<[Occupying Depts]={'Unallocated'}>}[Adj Net Size - SF])*sum([SF Percentage])

Re: Expression help

vishsaggi — Mon, 24 Jul 2017 19:00:40 GMT

Check this?

Sorry use this expr:

= Num((sum(TOTAL [Adj Net Size - SF]) - sum(TOTAL {< [Occupying Depts] = {'Unallocated'} >} [Adj Net Size - SF])) , '###0') * Subfield([SF Percentage], '%',1)

Re: Expression help

md_qlikview — Mon, 24 Jul 2017 19:02:19 GMT

Hi,

Try Below expression with Paranthesis

(sum(TOTAL {<[Occupying Depts]={'Unallocated'}>}[Adj Net Size - SF]))*sum([SF Percentage])

Re: Expression help

apthansh — Mon, 24 Jul 2017 19:05:30 GMT

I see that but thats not what I am looking for....you are not removing unallocated from total

Re: Expression help

manas_bn — Mon, 24 Jul 2017 19:09:32 GMT

Adding to the many suggestions:

Add a variable with this expression: This basically calculates (Total SF - Unallocated) and doesn't change with selections.

=sum({1}[Adj Net Size - SF])-Sum({1<[Occupying Depts]={'Unallocated'}>}[Adj Net Size - SF])

Use the variable in the chart.

=vVar*Num#(subfield([SF Percentage],'%',1))

Re: Expression help

antoniotiman — Mon, 24 Jul 2017 19:21:43 GMT

Hi Ansh,

see Attachment.

Regards,

Antonio.

Re: Expression help

apthansh — Tue, 25 Jul 2017 02:08:36 GMT

Thank you Antonio - But when I put this in Text box or do expression total it goes blank...can you please sujjest ?

Re: Expression help

antoniotiman — Tue, 25 Jul 2017 04:14:38 GMT

In Text Box

=Num(Sum(Aggr(
(Sum(TOTAL [Adj Net Size - SF])-Sum({<[Occupying Depts]={'Unallocated'}>} TOTAL [Adj Net Size - SF]))*
Num(Left([SF Percentage],5)),ParentID,Centers,[Occupying Depts])),'#,##0.00')

Re: Expression help

mangalsk — Tue, 25 Jul 2017 05:50:46 GMT

PFA working