topic Re: Set analysis expression question in QlikView

Set analysis expression question

Thu, 12 Nov 2015 01:43:33 GMT

Hi Everybody

I have a question about how to expression the following metrics through qlikview set analysis expression.

I want to get the formula to get:

total of value for all of the year dimension excluding the current year selection together.

For example, 2014 is selected in the current selection, Ontario is selected in the province selection.

We need to get the total of sales for all the years excluding 2014 together with Ontario.

One thing to note is that in the year dimension, there are some records whose year value is null.

for example, fact table like below

2014 ,Ontario, 100

2015, Ontario, 1000

null(), Ontario, 500

2015, Quebec, 200

I would like to see the result to be 1300.

Thanks.

Re: Set analysis expression question

arulsettu — Thu, 12 Nov 2015 06:04:23 GMT

if you want the result 1300

try this

=sum({<year={'*'}-{''}>}amount)

Re: Set analysis expression question

tresB — Thu, 12 Nov 2015 06:08:11 GMT

If the Year field has real null (IsNull(Year) returns -1, i.e. - true), you could use: Sum({<Year={'*'}>}Amount) ,

'*' excludes nulls.

Re: Set analysis expression question

PrashantSangle — Thu, 12 Nov 2015 06:10:36 GMT

Hi,

What is logic behind getting 1300??

also you are considering null() year in Total Value?

can you ellaborate little bit more..

Regards,

Re: Set analysis expression question

Thu, 12 Nov 2015 12:58:46 GMT

Yes. I need to include null() related records.

The use case can be:

Show me all the records including the one who has not been closed. (Closed date is null).

Thanks.

发自我的 iPhone

在 Nov 12, 2015，1:11 AM，max dreamer <qcwebmaster@qlikview.com> 写道：
Set analysis expression question
reply from max dreamer in App Development - View the full discussion
Hi,

What is logic behind getting 1300??
also you are considering null() year in Total Value?

can you ellaborate little bit more..

Regards,
Reply to this message by replying to this email, or go to the message on Qlik Community
Start a new discussion in App Development by email or at Qlik Community
Following Set analysis expression question in these streams: Inbox
© 1993-2015 QlikTech International AB | Copyright & Trademarks | Privacy | Terms of Use | Software EULA

Re: Set analysis expression question

Anonymous — Thu, 12 Nov 2015 13:03:19 GMT

Is seems to get 1300 you are skipping null values?

Use suppress Null option of dimension under Dimension tab

Re: Set analysis expression question

Anonymous — Thu, 12 Nov 2015 13:10:15 GMT

Well, if you want to include Null as well, then the value wud be 1800 not 1300..

Re: Set analysis expression question

Thu, 12 Nov 2015 13:39:21 GMT

Sorry everybody.

I would like to see the result to be 1500.

which includes all the ontario numbers exluding the record of 2014 and ontario.

Re: Set analysis expression question

Thu, 12 Nov 2015 15:18:34 GMT

Y r Right.

I Would add One more sentence that Ontario is sélectes at thé moment.

发自我的 iPhone

在 Nov 12, 2015，8:10 AM，Balraj Ahlawat <qcwebmaster@qlikview.com> 写道：
Set analysis expression question
reply from Balraj Ahlawat in App Development - View the full discussion
Well, if you want to include Null as well, then the value wud be 1800 not 1300..
Reply to this message by replying to this email, or go to the message on Qlik Community
Start a new discussion in App Development by email or at Qlik Community
Following Set analysis expression question in these streams: Inbox
© 1993-2015 QlikTech International AB | Copyright & Trademarks | Privacy | Terms of Use | Software EULA

Re: Set analysis expression question

maxgro — Thu, 12 Nov 2015 18:53:20 GMT

maybe

sum({1<reg=P(reg)> - $<year=P(year),reg=P(reg)>} val)

Re: Set analysis expression question

Fri, 13 Nov 2015 03:35:24 GMT

maxgro

Thank you for the answer. I works for the case.

while if there are multiple dimensions there, I have to specify everyone of

them in the expression like below:

*sum({1<reg=P(reg),dim1=p(dim1>,dim2=p(dim2),dim3=p(dim3),dim4=p(dim4)> -

$<year=P(year)>} val)*

*Is there any way that I don't need to specify the dimensions which are in

sync with user selection?*

Thanks.

Re: Set analysis expression question

PrashantSangle — Fri, 13 Nov 2015 04:32:59 GMT

Hi,

Try this in text object.

=sum({<Year=>}TOTAL Sales)-sum({<Year={"=$(=Year)"}>}Sales)

Regards

Re: Set analysis expression question

Fri, 13 Nov 2015 04:45:36 GMT

Hi Max

the aggregation I need is COUNT instead of SUM.

so the way you mentioned won't work.

Any idea about how to support count ?

Thanks.

On Thu, Nov 12, 2015 at 11:33 PM, max dreamer <qcwebmaster@qlikview.com>

Re: Set analysis expression question

Anonymous — Fri, 13 Nov 2015 07:19:03 GMT

Then replace sum() with count()

and Total Sales and Sales with your count measure