topic Using Set Analysis to get 13 months back Sales in QlikView

Using Set Analysis to get 13 months back Sales

Thu, 26 Nov 2009 20:59:41 GMT

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

Using Set Analysis to get 13 months back Sales

Thu, 26 Nov 2009 21:25:43 GMT

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

Using Set Analysis to get 13 months back Sales

Thu, 26 Nov 2009 22:07:43 GMT

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

Using Set Analysis to get 13 months back Sales

Miguel_Angel_Baeyens — Thu, 26 Nov 2009 22:33:17 GMT

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?

Using Set Analysis to get 13 months back Sales

Fri, 27 Nov 2009 01:13:57 GMT

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

Using Set Analysis to get 13 months back Sales

Fri, 27 Nov 2009 02:05:15 GMT

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.

Using Set Analysis to get 13 months back Sales

Miguel_Angel_Baeyens — Fri, 27 Nov 2009 04:14:26 GMT

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.

Using Set Analysis to get 13 months back Sales

Fri, 27 Nov 2009 05:29:06 GMT

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.

Using Set Analysis to get 13 months back Sales

Fri, 27 Nov 2009 12:51:54 GMT

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

Using Set Analysis to get 13 months back Sales

Miguel_Angel_Baeyens — Fri, 27 Nov 2009 15:28:04 GMT

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.

Using Set Analysis to get 13 months back Sales

Fri, 27 Nov 2009 18:10:40 GMT

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.

Using Set Analysis to get 13 months back Sales

Fri, 27 Nov 2009 18:53:50 GMT

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

Using Set Analysis to get 13 months back Sales

Fri, 27 Nov 2009 19:00:40 GMT

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

Using Set Analysis to get 13 months back Sales

Fri, 27 Nov 2009 19:32:04 GMT

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.

Using Set Analysis to get 13 months back Sales

Fri, 27 Nov 2009 19:44:53 GMT

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

Using Set Analysis to get 13 months back Sales

Wed, 09 Dec 2009 00:49:07 GMT

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.