topic Problem with a formula in QlikView

Problem with a formula

Wed, 31 Mar 2010 17:18:27 GMT

Hi everyone:

I'm using this formula in set analysis to calculate the sales of my previous selected month:

SUM({$<Year= {$(=max(Year) - $(=if(max(Month) = 1, 1, 0)))},Month = {$(=if((Month) = 1, 12, only(Month)-1) )}, MonthYear = {"<=$(=max(MonthYear))"}>} Sales)

Does anyone can help me to modify this formula´, i need to show the sales of "N" Previous Months, because mine it only works when you select 1 month???

For example, if I select DEC,NOV,OCT of 2009, the formula has to show JUL,AUG,SEP of 2009

Regards

Carlos

Problem with a formula

johnw — Wed, 31 Mar 2010 18:35:40 GMT

Maybe this?

sum({<Year=,Month=,MonthYear={"<$(=min(MonthYear)) >=$(=addmonths(min(MonthYear),1+max(MonthYear)-min(MonthYear)))"}>} Sales)

Problem with a formula

Wed, 31 Mar 2010 18:58:56 GMT

Almost John, with that formula you can get the sales up to the previous month of the selection, what i attempt to do is to get the sales for the same number of the month's selection but the previous one, may i make myself understable???

Example :

My Selection = JAN 2010, FEB 2010

Desire Column = NOV 2009, DEC 2009

Regards

Carlos

Problem with a formula

johnw — Wed, 31 Mar 2010 19:10:47 GMT

The formula I posted is intended to do that. Note this portion of it:

sum({<Year=,Month=,MonthYear={"<$(=min(MonthYear)) >=$(=addmonths(min(MonthYear),1+max(MonthYear)-min(MonthYear)))"}>} Sales)

1+max(MonthYear)-min(MonthYear) is calculating how many months are in the range you selected. We then subtract that many months from min(MonthYear). That gives us the starting point for the range.

Is it not working? It might have a bug in it, since I can't test it myself. I would suggest breaking down the part I highlighted above, and trying to figure out where it is going wrong.

Problem with a formula

Wed, 31 Mar 2010 19:17:58 GMT

Ok, I understand the point i will try this part and i tell you later, thanks

Carlos

Problem with a formula

Wed, 31 Mar 2010 20:16:51 GMT

I think here is the problem John but i dont get why the year goes to 2012, look i put this part of the formula in a chart to see the results and look:

any ideas???

Problem with a formula

johnw — Wed, 31 Mar 2010 20:25:21 GMT

Oh! Of course! MonthYear is a date. So 1+max(MonthYear)-min(MonthYear) is giving the DAYS between the two, not the MONTHS.

Hopefully you can debug from there. Very busy right now. Will debug later if you haven't.

Problem with a formula

Wed, 31 Mar 2010 23:45:43 GMT

I will appreciate if you can help with this when you have time plz John, its very important this plz

Regards

Carlos

Problem with a formula

johnw — Wed, 31 Mar 2010 23:56:47 GMT

How about this?

sum({<Year=,Month=,MonthYear={"<$(=min(MonthYear)) >=$(=addmonths(min(MonthYear),-count(distinct MonthYear)))"}>} Sales)

Problem with a formula

Thu, 01 Apr 2010 00:07:43 GMT

It is the same result of the other formula, it doesnt work, i put this:

addmonths(min(MonthYear),-GetSelectedCount(MonthYear))

and i can get the desire period of time but the formula doesnt work, i mean when i select JAN 2010, FEB 2010 i get DIC 2009, NOV 2009

but without the sum of the sales.

Problem with a formula

johnw — Thu, 01 Apr 2010 16:23:11 GMT

OK, all it needs is for the date to be correctly formatted to match your MonthYear. In the attached example, that means putting it in MMM YYYY format. If that's the format you're using, use this:

sum({<Year=,Month=,MonthYear={"<$(=min(MonthYear)) >=$(=date(addmonths(min(MonthYear),-count(distinct MonthYear)),'MMM YYYY'))"}>} Sales)

Otherwise substitute your own format.

Problem with a formula

Mon, 05 Apr 2010 16:58:51 GMT

Hi John:

It works perfect, thanks for all your support.

Regards,

Carlos