Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

set analysis (max year and last year)

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?

16 Replies
sunny_talwar

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

jsingh71
Partner - Specialist
Partner - Specialist

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

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

These two expression working fine for me.

Remove only() and use your function.

Not applicable
Author

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

sunny_talwar

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?

Not applicable
Author

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

Anonymous
Not applicable
Author

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.

sunny_talwar

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])

tresesco
MVP
MVP

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

krishna20
Specialist II
Specialist II

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..