topic Set Analysis Max Year, Month in QlikView

Set Analysis Max Year, Month

Tue, 18 Nov 2014 09:53:39 GMT

Hello,

I'm trying to do something that seems relatively simple but i cannot get it to work. I want to calculate the sum of a value only for the maximum year and month of the current selection. This is the expression i'm using,

sum({$<YEAR = {$(=max(YEAR))}, MONTH = {$(=max(MONTH))}>} Stock_Value_CY)

I;ve broken the expression in its compnenets and although the expression for max year works, the expression for max month doesn't.

Anybody can see what i;m doing wrong?

Re: Set Analysis Max Year, Month

PrashantSangle — Tue, 18 Nov 2014 09:56:15 GMT

Hi,

Try in ""

sum({$<YEAR = {"$(=max(YEAR))"}, MONTH = {"$(=max(MONTH))"}>} Stock_Value_CY)

Regards

Re: Set Analysis Max Year, Month

MK_QSL — Tue, 18 Nov 2014 09:56:51 GMT

Can you share your sample apps please?

Re: Set Analysis Max Year, Month

jyothish8807 — Tue, 18 Nov 2014 10:02:37 GMT

Hi Petrovic,

Your expression looks good , i think some issue with month field.Can you please share your app?

Try =max(MONTH) in a textbox and see what output you are getting?

Regards

Re: Set Analysis Max Year, Month

Tue, 18 Nov 2014 10:24:17 GMT

I've already tried this syntax, it doesn't work.

Re: Set Analysis Max Year, Month

Tue, 18 Nov 2014 10:26:23 GMT

I've done this and it gives me back the value i want. it is numeric, integer btw.

I've also tried making the calculations into variables and use the variables but it still doesn;t work. A set analysis expression using the year variable works but the month doesn;t.

Re: Set Analysis Max Year, Month

Tue, 18 Nov 2014 10:28:44 GMT

I can't upload the app because I'm working on a document taken from the customer which is too big to upload. I do not have a smaller data set.

Re: Set Analysis Max Year, Month

Tue, 18 Nov 2014 10:33:39 GMT

try this

i guess u made field mistakes

sum({$<YEAR = {$(=max(YEAR))}, MONTH = {$(=max(MONTH))}>} Stock_Value_CY)

do u have the field called month in capital letters?

Re: Set Analysis Max Year, Month

ashwanin — Tue, 18 Nov 2014 10:33:44 GMT

Hi Vasilis,

=max(year) provide you 2014 , while =max(month) provide you ' - '

Please add the field name from where you are taking the month

e,g =max(month(today()))

Re: Set Analysis Max Year, Month

Tue, 18 Nov 2014 10:40:25 GMT

These 2 fields are in capital letters and i've tried pasting the actual field names. The result is the same.

Re: Set Analysis Max Year, Month

Tue, 18 Nov 2014 10:42:31 GMT

the 2 fields are YEAR and MONTH in capitals. They're both integer and numeric and when used in =max() function they give me the result i want.

I've pasted the MONTH field with no change.

The result i;m getting is 0, not '-'

A set analysis using only max(YEAR) brings me results while an expression with only max(MONTH) brings me 0.

Re: Set Analysis Max Year, Month

PrashantSangle — Tue, 18 Nov 2014 11:25:55 GMT

Hi,

Can tell us

what value MONTH field contain?

and

What is output of =max(MONTH) in text box?

for Month field try like

=Month(max(MONTH))

=MonthName(max(MONTH))

Regards

Re: Set Analysis Max Year, Month

Tue, 18 Nov 2014 11:34:55 GMT

create an extra field in your script as

num(Month(Yourdatefield)) as Month#,

and reload the application and write your expression as

sum({$<YEAR = {$(=max(YEAR))}, Month# = {$(=max(Month#))}>} Stock_Value_CY)

hope it will work

Re: Set Analysis Max Year, Month

Fri, 21 Nov 2014 14:20:50 GMT

Thank you all for taking the time to respond.

Re: Set Analysis Max Year, Month

Anonymous — Wed, 07 Oct 2015 17:29:11 GMT

Thank you for this.. I've been spending hours for the answer to my problem.