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: 
bharatkishore
Creator III
Creator III

Selected Dates Range

Hi All,

I have a date field from Jan 2014 to Sep 2017.

I need to calculate sum(consumption) from Oct 2015 to Sep 2016

then I need to calculate sum(consumption) from Oct 2016 to Sep 2017

Can you please tell me how can i achieve this.

Attached app for more reference.

Thanks,

Bharat

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Try like:

Last year:

=Sum({<Year, Month,[MonthYear]={">=$(=MonthStart(Max(MonthYear),-11)) <=$(=MonthStart(Max(MonthYear)))"}>}Consumption)

Last to last year:

Sum({<Year, Month,[MonthYear]={">=$(=MonthStart(Max(MonthYear),-23)) <=$(=MonthStart(Max(MonthYear),-11))"}>}Consumption)

View solution in original post

11 Replies
tresesco
MVP
MVP

In text boxes? Are these ranges static or you have some logic to decide upon the ranges?

bharatkishore
Creator III
Creator III
Author

Hi Tresco,

It will be based on the dates selection,

For example I have dates starting from Jan 2014 to Sep 2017.

If i select Sep 2017 the first expression i.e. sum(consumption) should calculate last 11 months i.e. Oct 2016 to Sep 2017.

And in the second expression i.e. sum(consumption) should calculate last 11 months i.e. Oct 2015 to Sep 2016.


The same way if i select Aug 2017 date range should be Sep 2016 to Aug 2017

And second expression should be Sep 2015 to Aug 2016


Please let me know if you require anything more.

poojashribanger
Creator II
Creator II

Hi,

You are saying your fiscal year starts from October and ends in September

poojashribanger
Creator II
Creator II

Hi,

You are saying your fiscal year starts from October and ends in September

bharatkishore
Creator III
Creator III
Author

Hi Poojashri,

It is not the fiscal year.. the calculation should be done based on last 11 months from what ever date i select..

tresesco
MVP
MVP

Try like:

Last year:

=Sum({<Year, Month,[MonthYear]={">=$(=MonthStart(Max(MonthYear),-11)) <=$(=MonthStart(Max(MonthYear)))"}>}Consumption)

Last to last year:

Sum({<Year, Month,[MonthYear]={">=$(=MonthStart(Max(MonthYear),-23)) <=$(=MonthStart(Max(MonthYear),-11))"}>}Consumption)

bharatkishore
Creator III
Creator III
Author

Thanks a lot.. Thank you so much...

If you don't mind can you explain me the logic..

tresesco
MVP
MVP

I am lazy and bad in explaining too (especially when it is non-verbal). However, if you could ask a specific portion of the expression you could not follow, I might give a try.

bharatkishore
Creator III
Creator III
Author

Can you please tell me why we have taken these Year, Month and Month Year and why month start and max(monthyear) and how to know what number to give like how you have given 11

({<Year, Month,[MonthYear]={">=$(=MonthStart(Max(MonthYear),-11))