topic Re: Set analysis to ignore a dimension in QlikView

Set analysis to ignore a dimension

richard_chilvers — Wed, 24 Jan 2018 09:23:33 GMT

I have a chart table like this :

Year	Month	Value	Last Year's Total Value
2017	Dec	100	0
2017	Nov	120	0
2017	Oct	150	0
2017	Sep	200	0
....etc.
2016	Dec	120	1,250
2016	Nov	80	1,250
2016	Oct	150	1,250
2016	Sep	90	1,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.

Re: Set analysis to ignore a dimension

kaushiknsolanki — Wed, 24 Jan 2018 09:25:56 GMT

Hi,

try this.

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

Regards,

Kaushik Solanki

Re: Set analysis to ignore a dimension

richard_chilvers — Wed, 24 Jan 2018 09:35:24 GMT

Thanks for responding.

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

Re: Set analysis to ignore a dimension

kaushiknsolanki — Wed, 24 Jan 2018 09:38:01 GMT

Hi,

It does work for me in my app.

Can you share your application.

Regards,

Kaushik Solanki

Re: Set analysis to ignore a dimension

Anil_Babu_Samineni — Wed, 24 Jan 2018 09:43:03 GMT

How about this?

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

Re: Set analysis to ignore a dimension

sunny_talwar — Wed, 24 Jan 2018 12:39:00 GMT

May be you are looking for this

Below(Sum(Value), 12)

or better yet, use The As-Of Table

Re: Set analysis to ignore a dimension

richard_chilvers — Wed, 24 Jan 2018 13:38:17 GMT

This still produces zero values for 2017

Re: Set analysis to ignore a dimension

richard_chilvers — Wed, 24 Jan 2018 13:42:03 GMT

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.

Re: Set analysis to ignore a dimension

sunny_talwar — Wed, 24 Jan 2018 13:50:14 GMT

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?

Re: Set analysis to ignore a dimension

richard_chilvers — Wed, 24 Jan 2018 14:47:01 GMT

Hi Sunny

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

Re: Set analysis to ignore a dimension

sunny_talwar — Wed, 24 Jan 2018 14:48:43 GMT

In that case, kaushik.solanki‌'s response looks good to me... I wonder why it did not work for you

Re: Set analysis to ignore a dimension

swuehl — Wed, 24 Jan 2018 17:12:45 GMT

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.

Re: Set analysis to ignore a dimension

richard_chilvers — Tue, 30 Jan 2018 13:52:28 GMT

Hi Stefan

Following on:

I have a variable, vPriorYear = Year-1.

The expression $(vPriorYear) correctly evaluates to the year prior to the year in the dimension on each row of my table.

Please can you advise how I use this variable in set analysis to give the total of the field Value for the year prior to the Year in the dimension?

Your help is appreciated.