topic Re: SUM Criteria in App Development

SUM Criteria

Wed, 22 Jul 2015 21:54:51 GMT

Hi All, I have made a similar question before, but I don't know if this apply as the one before.

example data attached.

my question is about this formula:

SUM({<[Fiscal Year]={'$(=Year(TODAY()))'}>}[Netsales])

What I understand this is: Sum all Netsales that criteria for [Fiscal Year] is today's year is 2015. out Fiscal Year end on sept 30th, but if today were Oct 1st, 2015, that formula will still show year 2015.

so now, within the date table, I have Date field that can match the Fiscal Year field.

How can I do this?

Thanks

Re: SUM Criteria

sunny_talwar — Wed, 22 Jul 2015 22:14:35 GMT

You would want to see Sales for FY2016 after Oct 1st, 2015??? May be this:

Sum({<[Fiscal Year] = {"$(=Max([Fiscal Year]))"}>} Netsales)

Re: SUM Criteria

Wed, 22 Jul 2015 22:31:41 GMT

no work... tried that... my tables have fiscal year 2016.

Re: SUM Criteria

sunny_talwar — Wed, 22 Jul 2015 22:37:36 GMT

What exactly do you want to see?

Re: SUM Criteria

Wed, 22 Jul 2015 22:49:43 GMT

I want to see Fiscal Year 2015, without any need to have the filter selected. If I use Max Fiscal Year, I have 2016 in there. so this is giving me 2016 data.

the Date table have everything that can be matched, but don't know how to tell using expression that

sum all netsales that fiscal year is today's date.

So, once I have this information, if I reach Oct 1st, 2015 which is Fiscal Year 2016, it will go there automatically.

not sure if you get it?

thanks

Re: SUM Criteria

sunny_talwar — Wed, 22 Jul 2015 22:57:36 GMT

Try this may be:

Sum({<[Fiscal Year] = {"$(=If(Num(Month(Today())) = 10, Year(Today()) + 1, Year(Today())))"}>} Netsales)

This should give Fiscal Year 2015 until 30th Sept 2015 and on Oct 1st it should give you Fiscal Year 2016.

Is that what you wanted?

Re: SUM Criteria

Wed, 22 Jul 2015 23:12:20 GMT

ok... it works!

let's hope this work for next year. 😃

Re: SUM Criteria

sunny_talwar — Wed, 22 Jul 2015 23:59:42 GMT

Just for trying, see if this work and gives you 2016

Sum({<[Fiscal Year] = {"$(=If(Num(Month(AddMonths(Today(), 4))) = 10, Year(Today()) + 1,Year(Today())))"}>} Netsales)

If this gives you result for 2016, I don't see why the above expression won't work.

Best,

Sunny