Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
if you want the result 1300
try this
=sum({<year={'*'}-{''}>}amount)
If the Year field has real null (IsNull(Year) returns -1, i.e. - true), you could use: Sum({<Year={'*'}>}Amount) ,
'*' excludes nulls.
Hi,
What is logic behind getting 1300??
also you are considering null() year in Total Value?
can you ellaborate little bit more..
Regards,
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
Is seems to get 1300 you are skipping null values?
Use suppress Null option of dimension under Dimension tab
Well, if you want to include Null as well, then the value wud be 1800 not 1300..
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.
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
maybe
sum({1<reg=P(reg)> - $<year=P(year),reg=P(reg)>} val)