topic Re: Selected Dates Range in QlikView

Selected Dates Range

bharatkishore — Thu, 26 Oct 2017 06:45:42 GMT

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

Re: Selected Dates Range

tresB — Thu, 26 Oct 2017 06:48:49 GMT

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

Re: Selected Dates Range

bharatkishore — Thu, 26 Oct 2017 06:56:58 GMT

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.

Re: Selected Dates Range

poojashribanger — Thu, 26 Oct 2017 07:01:11 GMT

Hi,

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

Re: Selected Dates Range

poojashribanger — Thu, 26 Oct 2017 07:01:36 GMT

Hi,

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

Re: Selected Dates Range

bharatkishore — Thu, 26 Oct 2017 07:04:00 GMT

Hi Poojashri,

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

Re: Selected Dates Range

tresB — Thu, 26 Oct 2017 07:11:20 GMT

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)

Re: Selected Dates Range

bharatkishore — Thu, 26 Oct 2017 07:30:08 GMT

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

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

Re: Selected Dates Range

tresB — Thu, 26 Oct 2017 07:35:49 GMT

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.

Re: Selected Dates Range

bharatkishore — Thu, 26 Oct 2017 07:39:19 GMT

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

Re: Selected Dates Range

tresB — Thu, 26 Oct 2017 07:47:00 GMT

Year, Month are taken to disregard the selection in those fields. I.e. the calculation would be only based on MonthYear field's reference (defined in the range expression). Max() is used to tell monthstart() where it should start from to go behind (11 months as you need). For better understanding you might want to learn more about set analysis. Set Analysis: syntaxes, examples

Re: Selected Dates Range

bharatkishore — Thu, 26 Oct 2017 07:48:54 GMT

Thank u so much .... thanks for ur time n patience for explaining and thank u so much once again..