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]

Now I'm during the reload, so I can't check my script.

In script you have got MonthNames, so why don't you put the MonthNumber

It shuold simplify your problem

[#3]

Hi Magda,

Thanks for the suggestion, that was my first suggestion too and it will truly simplify my work as a developer, but in my company there are 100s of QV users whose work will be troubled, so I will try to achieve my goal without having to change MonthNames to numbers at first... but will come back to that solution if nothing else helps.

Best,

Maya

[Miguel_Angel_Baeyens]

Why don't you try to use function MonthName in your set analysis instead of Month? Is your data source having month names in cyrillic?

[#5]

Yes, all month names are in cyrillic, that's why i cant use MonthName

[#6]

When you use a Month only the respective month number is selected. For this you will have to create a month number field {num(month(date))}.
Now try using this month number with month in the set analysis code you have used.

I feel it should work fine...I have'nt tried it. I also feel users will not face a problem because of this. You are changing only the code but nothing from the front end. Making a copy of the application and trying it will be a better idea.
Will let you know if this works, soon.

[Miguel_Angel_Baeyens]

Why don't you load your month names into a table and then you reference them using peek()? That would allow you to keep your names as they are without bothering other users, and you can easily get the month number to get the correspondence with the table.

[#8]

Hello Maya.

I wonder if you could post your application to be analysed.

I have a suggestion but I'd like to look your application to be more effective.

Thanks.

Huberto.

[#9]

Hi all,

@Robinson
I tried it, but with no result... I have to get month name in Bulgarian as final result. I mean that I have to get month number first so I can get previous month number and then turn back to Month name, and all Month names are stored in cyrillic... I'm afraid this gets too confusing...

@Miguel
That's the simplest solution, but my application is huge and the reload takes up to 8 hours, so tests take days. I was hoping for there to be a function that can get element in an array. Month names are stored in an array, so if there is such function I can get the element number (index) and make my calculations with it... Is there such a function?

@Huberto
My application is huge (2Gb) and contains business sensitive data, so there is no way to upload it, but thanks for you trying to help.


Best,
Maya

[Miguel_Angel_Baeyens · Accepted solution]

Hello Maya,

You can use a code like

=SubField(MonthNamesInRoman, ';', Month(Date()))

Where MonthNamesInRoman is a variable like

SET MonthNamesInRoman='jan;feb;mar;apr;may;jun;jul;aug;sep;oct;nov;dec';

or whatever the names you want to use with names in roman characters and Month(Date()) my test to get the month number. I think that month number is not a problem as you can get it always by doing Month(timestamp). Anyway, let me know.

Regards.