topic Re: set analysis (max year and last year) in QlikView

set analysis (max year and last year)

Mon, 19 Oct 2015 10:33:24 GMT

Hi,

There something wrong in expressions I made:

This year sales amount:

Sum({$<Year={$(=Max(Year))}>}[Sales Amount])

Last year sales amount

Sum({$<Year={$(=Max(Year)-1)}>}[Sales Amount])

Sales amount is the same for both of them and it shouldn't be. And year is not max(year). Actually, this just gives a sum of all years or sum of selected year(s).

There was ei trigger on sheet (on opening) where max(year) was automatically selected, but I removed that.

What's wrong in the expressions?

Re: set analysis (max year and last year)

sunny_talwar — Mon, 19 Oct 2015 10:39:04 GMT

What is the format of your year field, is it 2012, 2013, 2014....? Would you be able to share a sample?

Re: set analysis (max year and last year)

jsingh71 — Mon, 19 Oct 2015 10:39:58 GMT

=Only({$<Year={$(=Max(Year)-1)}>}Year)

=Only({$<Year={$(=Max(Year))}>}Year)

These two expression working fine for me.

Remove only() and use your function.

Re: set analysis (max year and last year)

Mon, 19 Oct 2015 10:49:57 GMT

Yes, it is 2012, 2013... Unfortunately I can't make sample file right now.

Re: set analysis (max year and last year)

sunny_talwar — Mon, 19 Oct 2015 10:56:17 GMT

Only other thing I can think of is, if your Year and [Sales Amount] are not connected to each other. Can you may be share a snapshot of your datamodel?

Re: set analysis (max year and last year)

Mon, 19 Oct 2015 11:06:00 GMT

Sorry, I can't do that either now, but Sales Amount and Year are both in a same table.

Re: set analysis (max year and last year)

Anonymous — Mon, 19 Oct 2015 11:08:42 GMT

Hi,

-Can you please check the Date field is in which format .(DD/MM/YYYY)

-Upload your Calendar Script.

This may help to solve it.

Thanks,

Bunny.

Re: set analysis (max year and last year)

sunny_talwar — Mon, 19 Oct 2015 11:09:45 GMT

I don't see anything wrong with your syntax, the only thing you can try (although I don't think it will make any difference) is to add single quotes within your set analysis:

Sum({$<Year={'$(=Max(Year))'}>}[Sales Amount])

Re: set analysis (max year and last year)

tresB — Mon, 19 Oct 2015 11:12:43 GMT

Field name is case sensitive. Is it year or Year? And check :Preparing examples for Upload - Reduction and Data Scrambling

Re: set analysis (max year and last year)

krishna20 — Mon, 19 Oct 2015 11:17:05 GMT

Hi,

Just cross check your values by using the expression like

Sum({$<Year={'2015'}>}[Sales Amount])

what is the result for 2015.

Then check for 2014.

you may find out where the mistake will goes..

Re: set analysis (max year and last year)

maxgro — Mon, 19 Oct 2015 11:31:43 GMT

From what you wrote, same amount for max(year) and max(year) -1),

it seems your set modifiers (Year={$(=Max(Year)-1)}) doesn't work

Maybe your field isn't Year (year?) or the field is Year but you don't have an association between Year (the table with Year field) and Sales Amount (the table with ...)

Re: set analysis (max year and last year)

Thu, 22 Oct 2015 06:45:52 GMT

Sum({$<Year={'2015'}>}[Sales Amount]) - expression works, but (Year={$(=Max(Year))}) doesn't. There is no calendar at all. Year field is in "fact" table and that's what I am using.

Re: set analysis (max year and last year)

maxgro — Thu, 22 Oct 2015 18:07:38 GMT

remove the label of your expression (with max(Year)), go over the label with the mouse and look at the value you get for Max(Year) to check if you get something

or post a small example of your problem

(this can be useful to reduce and protect the data Preparing examples for Upload - Reduction and Data Scrambling)

Re: set analysis (max year and last year)

erjohnso — Fri, 30 Oct 2015 18:39:04 GMT

Have you tried using max(Year) and max(Year,2) which calls the rank function instead of relying on the calc of Year-1?

Re: set analysis (max year and last year)

lars_plenge — Mon, 23 Nov 2015 12:09:43 GMT

This is working for me

last year:

Calender.Year = {$(#=Only(Num(Calender.Year)-1))

current year:

Calender.Year = {$(#=Only(Num(Calender.Year)))

Re: set analysis (max year and last year)

Mon, 02 May 2016 06:17:51 GMT

hello ville,

make use of this expression to get max year sales

=sum({<year={"$(=max({<year>}year))"}>}[sales revenue])

regards

Ashok

Re: set analysis (max year and last year)

jim_chan — Mon, 17 Oct 2016 09:12:35 GMT

Sunny,

what if the same expression , and i need to add in max(Month) as well?

count({$<Year={'$(=Max(Year))'}>}ref_number)

Rgds,

Jim