set analysis (max year and last year)

[#1]

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?

[sunny_talwar]

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

[jsingh71]

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

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

These two expression working fine for me.

Remove only() and use your function.

[#4]

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?

[#6]

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

[Anonymous]

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]

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

[krishna20]

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