topic Re: Max month in QlikView

Max month

Anonymous — Thu, 01 Feb 2018 17:16:36 GMT

I have the following:

=NUM(SUM({$<CommonMonth={$(=max(CommonMonth))}, CommonMonth= >} RetainedAccounts)/Sum({<CommonMonth = {"$(=date(AddYears(Date(DATE#(CommonMonth,'YYYY/MM')),-1),'YYYY/MM'))"}>} NumAccts),'#0%')

The first half calculates correctly, it finds the maximum month in my dataset and sums the retained accounts. The second part I want to do the same thing but look 1 year back (Current month but the previous year). I have tried to insert the MAX function into the second part of the statement (red text) with no luck. My aim is to have a static value no matter what date you select as it will always show the MAX date from my dataset.

Re: Max month

sunny_talwar — Thu, 01 Feb 2018 17:35:02 GMT

If CommonMonth is not a dimension in the chart, you can try this

=Num(Sum({$<CommonMonth={$(=max(CommonMonth))}>} RetainedAccounts)/Sum({<CommonMonth = {"$(=Date(AddYears(Max(CommonMonth), -1), 'YYYY/MM'))"}>} NumAccts),'#0%')

Re: Max month

sunny_talwar — Thu, 01 Feb 2018 17:36:35 GMT

For making this static, you can try this

=Num(Sum({$<CommonMonth = {$(=Max({<CommonMonth>}CommonMonth))}>} RetainedAccounts)/Sum({<CommonMonth = {"$(=Date(AddYears(Max({<CommonMonth>}CommonMonth), -1), 'YYYY/MM'))"}>} NumAccts),'#0%')

or this

=Num(Sum({$<CommonMonth = {$(=Max({1}CommonMonth))}>} RetainedAccounts)/Sum({<CommonMonth = {"$(=Date(AddYears(Max({1}CommonMonth), -1), 'YYYY/MM'))"}>} NumAccts),'#0%')

Re: Max month

Anonymous — Fri, 02 Feb 2018 10:40:02 GMT

Hi - I still need to convert the data from YYYY/MM format into normal date,subtract a year, then convert it back to YYYY/MM which is in my original formula, how would I apply the MAX in that case?

Re: Max month

sunny_talwar — Fri, 02 Feb 2018 16:52:21 GMT

Not sure I follow... would you be able to share a sample?

Re: Max month

Anonymous — Fri, 02 Feb 2018 17:01:39 GMT

=NUM(SUM({$<CommonMonth={$(=max(CommonMonth))}, CommonMonth= >} RetainedAccounts)/Sum({<CommonMonth = {"$(=date(AddYears(Date(DATE#(CommonMonth,'YYYY/MM')),-1),'YYYY/MM'))"}>} NumAccts),'#0%')

The bit in red, this works, but I just need to add the MAX bit in, just not sure where. I need to keep the DATE# part as my COMMONMONTH is in this format YYYY/MM, so when DATE tries to subtract a year, it doesn't understand the format, so I need to keep that bit in, but in your answer the DATE# part was removed, so if I keep the formula the same, but just add MAX, where would I put it? As I tried your formulas and they do not work for me for this reason. Thanks

Re: Max month

sunny_talwar — Fri, 02 Feb 2018 18:18:23 GMT

Don't think I understand... may be someone else can help you better

Re: Max month

Anonymous — Fri, 02 Feb 2018 18:28:42 GMT

For the second bit of my formula (red bit) I need to look at the maximum month for example would now be 2018/02 and then select the date one year previous so 2017/02. No matter what date selection a user picks on the CommonMonth selection, it should always show the calculation as the Maximum month in the dataset one year back.

Re: Max month

sunny_talwar — Fri, 02 Feb 2018 18:31:30 GMT

Try this

=Num(Sum({$<CommonMonth = {$(=Max(CommonMonth))}>} RetainedAccounts)/Sum({<CommonMonth = {"$(=Date(AddYears(Max({1}CommonMonth), -1), 'YYYY/MM'))"}>} NumAccts),'#0%')

Re: Max month

Anonymous — Fri, 02 Feb 2018 18:53:38 GMT

=NUM(SUM({$<CommonMonth={$(=max(CommonMonth))}, CommonMonth= >} RetainedAccounts)/Sum({<CommonMonth = {"$(=date(AddYears(Date(DATE#(MAX({1}CommonMonth,'YYYY/MM'))),-1),'YYYY/MM'))"}>} NumAccts),'#0%')

I get '-' when I do your last suggestion... above is what I did. I need DATE# to convert to string

Re: Max month

sunny_talwar — Fri, 02 Feb 2018 19:02:26 GMT

Why do you need to do that? If your top expression is working with Max(CommonMonth), it means that your date is read correctly as a date...

But anyways, try this

=Num(Sum({$<CommonMonth={$(=max(CommonMonth))}, CommonMonth= >} RetainedAccounts)/Sum({<CommonMonth = {"$(=Date(AddYears(Date(Date#(Max({1}CommonMonth),'YYYY/MM')), -1), 'YYYY/MM'))"}>} NumAccts),'#0%')

Re: Max month

Anonymous — Fri, 02 Feb 2018 19:06:27 GMT

Because I am trying to look 1 year back, I understood previously that I would have to convert my string YYYY/MM into a date, go back one year.

I still get a '-' when I use the revised formula.

Re: Max month

sunny_talwar — Fri, 02 Feb 2018 19:07:56 GMT

Unless I see... I won't know.... Sorry

Re: Max month

sunny_talwar — Fri, 02 Feb 2018 19:09:49 GMT

Some threads for look at

QlikView Date fields

Get the Dates Right

Why don’t my dates work?

Dates in Set Analysis