topic Re: Using set analysis to get Max Value regardless of selection in QlikView

Using set analysis to get Max Value regardless of selection

Thu, 29 May 2014 23:09:58 GMT

Hello!

I'm trying to compare Prior Year and Current year, so what I'm doing is grabbing the current [Year Month] selection, subtracting it by 100

and using set analysis to force it onto the expression:

=num(sum({<[Year Month] = {$(=v_MaxYearMonth-100)}, [Type] = {'P'}>}[Count]),'#,##0')

I have set as v_MaxYearMonth =Max({1}[Year Month])

I verified that v_MaxYearMonth works, but the sum expression does not for all [Year Month] selections, it returns the sum only if the selection is 201305 (which incidentally is this 201405 - 100), but returns 0 otherwise

Maybe I'm missing something obvious, but I've been pondering on this for hours, and I got nothing...

Any ideas?!

Re: Using set analysis to get Max Value regardless of selection

SergeyMak — Fri, 30 May 2014 01:19:25 GMT

To get same month of previous year I usually use Month={$(=Month(AddMonths(Max(Calendar.Date), -12)))},

Year={$(=Year(AddMonths(Max(Calendar.Date), -12)))}, Calendar.Date=

If you'd like advice for your approach, please share a sample app.

Re: Using set analysis to get Max Value regardless of selection

jagan — Fri, 30 May 2014 01:46:39 GMT

HI,

Why you are subtracting 100 from v_MaxYearMonth, do you want what is your Year Month format, if you use -100 the month is returned as numeric value, not as month, that is why the expression is not working, can you attach some sample data and expected output.

Try this expression, hope it helps you

=num(sum({<[Year Month] = {"$(=Date(Date#(v_MaxYearMonth, 'YYYYMM')-100, 'YYYYMM'))"}, [Type] = {'P'}>}[Count]),'#,##0')

Regards,

Jagan.

Re: Using set analysis to get Max Value regardless of selection

Fri, 30 May 2014 02:05:25 GMT

Thank you, actually my year month format is YYYYMM so for example current year month is 201405

Re: Using set analysis to get Max Value regardless of selection

jagan — Fri, 30 May 2014 02:13:54 GMT

Hi,

Is that expression working?

Regards,

Jagan.

Re: Using set analysis to get Max Value regardless of selection

Fri, 30 May 2014 02:16:42 GMT

No, that expression didn't work.

I also have Month and Year as separate values (i get to them by trimming {Year Month]) I got an idea of putting "Year = , Month = " in the set analysis, but that didn't work either.

Re: Using set analysis to get Max Value regardless of selection

Fri, 30 May 2014 02:19:18 GMT

For the record, it works when i DO select 201305 in my selections.. WEIRD..

Re: Using set analysis to get Max Value regardless of selection

jagan — Fri, 30 May 2014 02:22:50 GMT

Hi,

Why you are subtracting -100 in your expression? Can you comeup with sample file and expected output?

Regards,

jagan.

Re: Using set analysis to get Max Value regardless of selection

Fri, 30 May 2014 02:26:40 GMT

subtracting 100 to get to last year. i'm trying to show the results of last year compared to this year.

So,

When a user selects 201405, i have one expression showing a result for that. then i want to use a set expression to show what happened in 201305

Re: Using set analysis to get Max Value regardless of selection

jagan — Fri, 30 May 2014 02:33:21 GMT

Hi,

Try this

=num(sum({<YearDimensionName={'$(=Max(YearDimensionName) -1)'}, [Type] = {'P'}>}[Count]),'#,##0')

Replace Year with your YearDimensionName.

=num(sum({<[Year Month] = {"$(=Date(MonthStart(Date#(v_MaxYearMonth, 'YYYYMM')-100, -12), 'YYYYMM'))"}, [Type] = {'P'}>}[Count]),'#,##0')

Regards,

Jagan.

Re: Using set analysis to get Max Value regardless of selection

Fri, 30 May 2014 02:37:49 GMT

BINGO!!! you got it!!! thank you Jagan Mohan

FYI: I just set the Year to -1 as per your first suggestion