Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
richard_chilvers
Specialist
Specialist

Set analysis to ignore a dimension

I have a chart table like this :

YearMonthValueLast Year's Total Value
2017Dec1000
2017Nov1200
2017Oct1500
2017Sep2000
....etc.
2016Dec1201,250
2016Nov801,250
2016Oct1501,250
2016Sep901,250
etc.....

In the last column, I would like always to have the total value for 2016, but for Year=2017, the column has zeroes.

I am using the expression:

SUM( {1 <Year = {'2016'} > } Value)

So the expression is being affected by the Year dimension. How do I ignore this dimension?

Thanks.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

What do you see if you put

=SUM( {1 <Year = {'2016'} > } Value)


in a text box?


You should see this value also in the least granular partial sum row in your table chart.


If you do, then Kaushik's expression using the TOTAL qualifier should work.

If not, then please post a sample QVW.




View solution in original post

12 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

try this.

SUM( {1 <Year = {'2016'} > } total Value)


Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
richard_chilvers
Specialist
Specialist
Author

Hi

Thanks for responding.

Your expression gives me the total for both years (ie. 2016 + 2017). Seems to ignore the set analysis.

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

It does work for me in my app.

Can you share your application.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Anil_Babu_Samineni

How about this?

Alt(Sum(TOTAL <Year>  {<Year={2016}>} Value),0)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

May be you are looking for this

Below(Sum(Value), 12)

or better yet, use The As-Of Table

richard_chilvers
Specialist
Specialist
Author

This still produces zero values for 2017

richard_chilvers
Specialist
Specialist
Author

Hi Sunny

Thanks as always for your response. I can see that both your suggestions will probably work, although I haven't tried them.

I felt my expression should work and was hoping that someone would correct an error. I must not understand the purpose of {1} in a set analysis.

sunny_talwar

Would be able to point out what your desired output is/was? What you have posted above is what you are getting, but what do you need?

richard_chilvers
Specialist
Specialist
Author

Hi Sunny

I need the total value for 2016 (ie. 1,250) to appear in the last column of every row.