Using Set Analysis to get 13 months back Sales

[#1]

Hello,

I'm new to QlikView and I will be really grateful if there is anyone who could (and is willing to) help me.

I'm trying to get the sales for a selected month compared to those made 13 months ago... Lets say the selected month is March 2009, I need a column representing the sales for February 2008. To accomplish this I'm using such an expression:

Sum({<Year={$(=Only(Year)-1)}, Month={$(=Only(Month)-1)}>} Sales)

I guess the problem is that the Month names are string values and may be that's the reason for not being able to extract 1 (Month-1) to get previous month name.

In the loading script I have this line

SET MonthNames='??????;????????;????;?????;???;???;???;??????;?????????;????????;???????;????????';

(These are months names in Bulgarian)

and I guess all Month numbers are stored just like names, so I don't know how to get their number.

Your advise will be greatly appreciated

Maya

[#2]

Hi Maya.

I developed an example to help you. So, let's go there. 🙂

1. I created a variable where you can specify how many months you would like to return;
2. This variable was used inside the expression to make flexible your application;
3. To return several months you can use the function addmonths.

These are the main points and the complete expression is what you can see ahead:

sum( {$< Year = {$(=year(addmonths(makedate(max(Year), max(Month), 1), -$(MonthsToReturn))))},
Month = {$(=month(addmonths(makedate(max(Year), max(Month), 1), -$(MonthsToReturn))))}>} [Sales Amount])

Give a look in the application. In the first column you can see the Sales of the selected month (this is your reference to return 13 months) and, in the second column are showed the Sales of 13 months before.
Use the variable at the left side to increase or decrease the number of months.

I guess your calendar should not be a problem in this way. I hope so.

Let me know if it works. 🙂

Huberto.

[#3]

Hi Miguel,

Thank you very much for your help. I solved the puzzle thanks to your advices. The final expression is:

Sum({<Year={$(#=Only(if(Month=1,Year-1,Year))-1)}, Month={$(=only(subfield (MonthNames,';',if(Month=1,12,Month-1))))}>}Sales)

but I would never had come to that without your help.

Best,
Maya

[#4]

Hi Huberto,

Thanks for the application, it might appear to be useful for solving some other issues in the near future, as I'm still learning (my first "contact" with QV was 3 weeks ago).

Best,

Maya

[#5]

Hi Maya.

I'd like to know if you tried my suggestion in your application.

It's just for curiosity because the month names are different and I'd like to know if they affect the expression that I proposed.

And, you're welcome. 😉

Huberto.

[#6]

Hi Huberto,

I have not tried it yet, but what I've tried was max(Month) and returns a number despite my month names being stored as cyrillic names. Next time I have issues with months and years I will have your advice and application in mind.

Thanks again!

Maya

[#7]

Hi Maya.

I was reading your post again and thinking about it. I guess I understood the problem caused by your month names. So, I changed my example to use your calendar (cyrillic names).

I'm attaching the new file for you to give a look. Just to think…

Huberto.