topic Re: Subtract from AddMonths with variable in QlikView

Subtract from AddMonths with variable

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I'm working with dates.

I have MonthYear ('MMM-YYYY').

In this example I've choose from apr-2016 to jun-2016.

I've created a function MonthDiff that gives me the number of months selected, in this case, 3.

I want to get the previous "MonthDiff" months from the date I've selected.

Example:

If I choose from apr-2016 to jun-2016, so I want previous to be jan-2016 to mar-2016.

Or if I choose from jan-2016 to jun-2016, I want previous to be jul-2015 to dez-2015.

Going back to my MonthDiff = 3

I want to do the next expression to get the value jan-2016

Date(min(addmonths(MonthYear,-$(vMonthDiff))), 'MMM-YYYY')

But this is not working, it's giving me the error "Error: Error in expression: Nested aggregation no allowed"

How can I solve this problem and achieve what I want?

Is it possible?

Thanks in advance,

Sílvia

Re: Subtract from AddMonths with variable

Anonymous — Fri, 18 Nov 2016 10:54:04 GMT

What does your $(vMonthDiff) expand to ?

The expression itself looks fine, as long as it expands to an integer.

Re: Subtract from AddMonths with variable

Anil_Babu_Samineni — Fri, 18 Nov 2016 11:01:14 GMT

Have you created variable there, does the variable gives the 3 at this situation. Can you please let me know the value or string you are getting got that

Re: Subtract from AddMonths with variable

hemanthaanichet — Fri, 18 Nov 2016 11:01:39 GMT

Hi Siliva,

exp 1 :SUM({<Year = {$(=only(Year))}, Month = {"<=$(=num(Month))"}>} [Sales Amount])

exp 2: SUM({<Year = {$(=only(Year)-1)}, Month = {"<=$(=num(Month))"}>} [Sales Amount])

if select year = 2016 month = nov

exp 1 shows sum of sales from jan to nov individually

exp2 shows previous year sum of sales from jan to nov individually

you have month year as same column

sum( monthyear = {"<=$(=(monthyear))"}[sales amount])

sum( monthyear = {"<=$(=(monthyear)-1)"}[sales amount])

Regards

Hemanth

Re: Subtract from AddMonths with variable

Anonymous — Fri, 18 Nov 2016 11:06:47 GMT

Hi,

This is my MonthDiff Variable:

= Num(((year(max(MonthYear)) * 12) + month(max(MonthYear))) - (((year(min(MonthYear)) * 12) + month(min(MonthYear)))) + 1)

In a separated text object, is giving me the value 3

Re: Subtract from AddMonths with variable

Anonymous — Fri, 18 Nov 2016 11:23:06 GMT

Your variable expression is being expanded to its text content and thus contains aggregation functions such as max which mean you are trying to nest aggregations which is not allowed as the error message says.

Try putting an equals sign before the variable to evaluate it to an integer

Date(min(addmonths(MonthYear,-$(=vMonthDiff))), 'MMM-YYYY')

Also have a look at this Blog post, The Little Equals Sign

Re: Subtract from AddMonths with variable

Anonymous — Fri, 18 Nov 2016 11:40:06 GMT

Hi Bill,

I've tried what you've suggested, but is still not working.

What am I doing wrong?

Re: Subtract from AddMonths with variable

hemanthaanichet — Fri, 18 Nov 2016 11:43:54 GMT

Hi Siliva,

try this:

=date(min(addmonths(Disbursement_MonthYear,-$(Display2))),'MMM-YYYY')

Dispaly2 : =Num(((year(max(Disbursement_MonthYear)) * 12) + month(max(Disbursement_MonthYear))) - (((year(min(Disbursement_MonthYear)) * 12) + month(min(Disbursement_MonthYear)))) + 1)

it the above expression it shows expression ok in text box

regards

Hemanth

Re: Subtract from AddMonths with variable

sunny_talwar — Fri, 18 Nov 2016 11:49:14 GMT

How about this?

Date(AddMonths(Min(MonthYear),-$(vMonthDiff)), 'MMM-YYYY')

Re: Subtract from AddMonths with variable

Anonymous — Fri, 18 Nov 2016 11:58:07 GMT

Hi Sunny,

Thanks a lot, your suggestion works perfectly

Regards,

Sílvia