Set analysis expression question

[#1]

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.

[arulsettu]

if you want the result 1300

try this

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

[tresesco]

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

'*' excludes nulls.

[PrashantSangle]

Hi,

What is logic behind getting 1300??

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

can you ellaborate little bit more..

Regards,

[#5]

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

[Anonymous]

Is seems to get 1300 you are skipping null values?

Use suppress Null option of dimension under Dimension tab

[Anonymous]

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

[#8]

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.

[#9]

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

[maxgro]

maybe

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