topic Re: Set Analysis not working in QlikView

Set Analysis not working

Tue, 01 Mar 2016 17:56:17 GMT

Hello Everyone,

I have this YTD formula working very well. _YYYY and _MM is calendar.

=sum ( {$<[Year] = {$(#=Only(_YYYY))}, [Month] = {"<=$(#=Only(_MM))"}>} [Rev-Actual])

When I do current month, it doesn't work:

=sum ( {$<[Year] = {$(#=Only(_YYYY))}, [Month] = {"$(#=Only(_MM))"}>} [Rev-Actual])

Can you help?

Thank you very much in advance.

Re: Set Analysis not working

sunny_talwar — Tue, 01 Mar 2016 17:59:22 GMT

Not sure, but what about this:

=Sum({$<[Year] = {$(#=Only(_YYYY))}, [Month] = {"=$(#=Only(_MM))"}>} [Rev-Actual])

Re: Set Analysis not working

swuehl — Tue, 01 Mar 2016 18:01:57 GMT

How do the Month and _MM field values look like?

If you put your expression in a straight table chart with no expression label, then hover with the mouse over the expression column header, what is displayed with dollar sign expansions performed?

Re: Set Analysis not working

Tue, 01 Mar 2016 18:04:47 GMT

Thank you - Sunny,

Your formula can bring data in but cannot change when click on Month bar. It just show one month data and not change.

Do you have a second thoughts?

Appreciate your help.

Re: Set Analysis not working

Tue, 01 Mar 2016 18:06:50 GMT

swuehl,

_MM is 1,2, 3...12.

This is a calendar and I have month bar on my app. I want to see different month revenue by click on month.

Thanks.

Re: Set Analysis not working

swuehl — Tue, 01 Mar 2016 18:09:22 GMT

Ok, so I assume Month field values are also 1,2,3,...12 and the expanded expression looks like

=sum ( {$<[Year] = {2016}, [Month] = {"3"}>} [Rev-Actual])

Right?

edit:

If so, could you detail what is not working? And have you checked that you actually have data for that current month?

Re: Set Analysis not working

Tue, 01 Mar 2016 18:19:18 GMT

swuehl,

I checked on Month is 'Jan' three letter but I have PdNo is number for each month.

sum ( {$<[Year] = {$(#=Only(_YYYY))}, [PdNo] = {'$(#=Only(_MM))'}>} [Rev-Actual])

This formula is not working too.

Thanks.

Re: Set Analysis not working

sunny_talwar — Tue, 01 Mar 2016 18:21:28 GMT

What if you do this:

Sum({$<[Year] = {$(#=Only(_YYYY))}, [PdNo] = {'$(#=Only(PdNo))'}>} [Rev-Actual])

Re: Set Analysis not working

Tue, 01 Mar 2016 18:34:11 GMT

Sunny,

This one can not bring any value but 0.

Thank you very much.

Re: Set Analysis not working

swuehl — Tue, 01 Mar 2016 18:41:24 GMT

Please

a) describe what you mean with 'not working': Computer crashes, expression returns zero, expression returns <> zero but other than expected (then also describe how you got your expected results)

b) check that =Only(_MM) returns a value that matches a value from PdNo.

Try making selections in Year and PdNo manually and check the result of sum([Rev-Actual])

c) Check that you haven't made other selections, potentially interfering with your set analysis

It would make things much easier if you could post a small sample QVW that demonstrates your issue

Re: Set Analysis not working

cupelix-qlik — Tue, 01 Mar 2016 19:30:13 GMT

Struggling to understand what your trying to get to.

If it's a case of you want a Year to Date figure to appear when someone selects a single Year & single Month. If so then I did have this working based on your formula.

=Sum( {$<Year = {$(#=Only(Year))},Month = {"<=$(#=Only(Month))"}>} Distance)

My Month field is 3 letters, Distance was just the first Integer field I came to. Year is just the 4 digits.

Re: Set Analysis not working

Tue, 01 Mar 2016 19:46:24 GMT

Swuehl,

I just found my Month was locked for some reason. I don't know when this Month was locked.

I am keep working on this lock problem.

Thanks,

Zili

Re: Set Analysis not working

Wed, 02 Mar 2016 02:36:09 GMT

Hello Everyone,

I just found this app has one bookmark locked the Month and has conflict on Month selection.

Sunny always has very good set analysis solution. swuehl helped me how to solve this problem step by step.

Lee Seagrave's suggestion also very valuable on my question.

Thank you all again. I appreciate your help.