Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

SUM Criteria

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

1 Solution

Accepted Solutions
MVP
MVP

Re: SUM Criteria

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?

7 Replies
MVP
MVP

Re: SUM Criteria

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

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

Not applicable

Re: SUM Criteria

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


MVP
MVP

Re: SUM Criteria

What exactly do you want to see?

Not applicable

Re: SUM Criteria


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

MVP
MVP

Re: SUM Criteria

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?

Not applicable

Re: SUM Criteria

ok... it works!

let's hope this work for next year. =)

MVP
MVP

Re: SUM Criteria

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