topic Re: Limiting data using Only-function in QlikView

Limiting data using Only-function

Mon, 09 May 2011 10:44:05 GMT

Hi,

I have sales data that I'm trying to limit in table/chart using Only-function in Expression.

Sum

({$<[YEAR]={$(=Only([YEAR])) }>} Sales)

Now I'm trying to limit the data also with month, but without any success. Is it possible to limit things using Only-function several times like this?

Sum

( if ([YEAR]= [YEAR]-1, {$<[MONTH]={$(=Only([AsOfMonth])) }>} Sales )

I know, this does not work. The idea here is to limit year to be last year and month should be selected.

Second question: I shoul also calculate last 12 months sales from same table. There is date, month, year etc available. What would be the best way to do that?

Here is the sample report.

Re: Limiting data using Only-function

IAMDV — Mon, 09 May 2011 12:01:41 GMT

Hi,

I had very similar requirement sometime back. And I had used variables to get this working. I had created some variables like this...

vCurrentYear : Max({1} Year)

vPreviouYear : Max({1} Year) - 1

vCurrentMonth : Max({1<Year = {$(=$(vMaxYear))}>} MonthNumber)

vPreviouMonthLastYear : IF($(vCurrentMonthNumber) = 1, 12, $(vCurrentMonthNumber) - 1)

I had declared the variables in the load script and everytime I load the data...variables will be updated. Then I had variables within my expression and calculated the sales data for the above time periods.

I hope this helps!

Good luck.

Cheers - DV

Re: Limiting data using Only-function

IAMDV — Mon, 09 May 2011 12:18:26 GMT

If you need further help, then please upload your QV document with some data. I'll can look into the QV document.

Cheers - DV

Limiting data using Only-function

Mon, 09 May 2011 13:54:36 GMT

Hi,

You'll find a sample from first message. I hope it's understandable. First problem is to limit data to one year and month at the same time in column "Month last year". Second is to get sum of last 12 months into last column. I've tried to solve these problem on report so far. For cumulative numbers I made a "asof" table according to instructions on this site and that helped with that problem. Please ask, if you need more information.

BR, Ville

Re: Limiting data using Only-function

Tue, 10 May 2011 11:03:26 GMT

If there is a solution, I would like to know about it...

Re: Limiting data using Only-function

IAMDV — Tue, 10 May 2011 11:23:33 GMT

Ville - Sorry I was busy with work. I am not sure if I had fully understood the requirement. But I had created an expression for [Month last year] column in the QV document for Fakta table. Here is the expression...

Sum({1<[MONTH]={$(=Only([AsOfMonth]))}, YEAR ={$(=($(vMyYear))-1)} >} LaskutettuSumma)

Before using this expression, I had created a variable in Variable Overiew Window (Although you can move this variable in the Load Script, because you just need to evaluate the variable only once when you load the data. This variable calculates the maximum year.

vMyYear = Max({1} YEAR)

Now irrespective of whatever year I select I would see the Last Year Same Month's data for each dimension listed.

Please let me know if this is what you needed.

Cheers - DV

Re: Limiting data using Only-function

Tue, 10 May 2011 11:49:44 GMT

Thanks, it works perfectly and it does exactly what it should!!!!

I had second question here as well about calculating last 12 months from same data. Here is the original question:

"Second question: I shoul also calculate last 12 months sales from same table. There is date, month, year etc available. What would be the best way to do that?"

It is the last column in same report, but is just the same as in Cumulative-column?

Re: Limiting data using Only-function

IAMDV — Tue, 10 May 2011 12:12:52 GMT

I am glad that it worked. Thanks for the points as well.

Here is the idea for the second question. I am assuming that we are not changing the data model, I mean we can add autonumber() for each month in the load script and you can use the monthnumber. However, I am not going that route for this solution. I prefer working on expression without tweaking the original data model.

I am just creating a new variable which calculates the current month.

vCurrentMonth = ONLY({$} AsOfMonth)

And then I am using the Dollar Sign Expansion functionality in QlikView to calculate the data.

Sum({1<YEAR ={$(=($(vMyYear)))}, MONTH = {'<= $(=($(vCurrentMonth))) '} >} LaskutettuSumma)

Sum({1<YEAR ={$(=($(vMyYear))-1)}, MONTH = {'>= $(=($(vCurrentMonth))) '} >} LaskutettuSumma)

I had not tested the expression. However this should work and let me know if this helps!

Good luck.

Cheers - DV

Re: Limiting data using Only-function

Wed, 11 May 2011 06:01:41 GMT

Thanks again! It worked fine without testing as well, so why bother...

Re: Limiting data using Only-function

IAMDV — Wed, 11 May 2011 08:37:31 GMT

Excellent!

Limiting data using Only-function

Wed, 11 May 2011 12:30:36 GMT

One more question and almost like previous.

I should calculate a four weeks/one month average on week level like this: "Sum(four week sales)/4".

I tried to modify the "12 months"-code, but without success. Should it be something like this?

Variables:

vCurrentMonth = ONLY({$} AsOfMonth)

vCurrentWeek = ONLY({$} WEEK)

Formula:

Sum({1<YEAR ={$(=($(vMyYear)))}, MONTH = {'<= $(=($(vCurrentMonth))) '} >} , WEEK= {'<= $(=($(vCurrentWeek))) '} >} LaskutettuSumma)

Sum({1<YEAR ={$(=($(vMyYear))-1)}, MONTH = {'>= $(=($(vCurrentMonth))) '} >}, WEEK= {'>= $(=($(vCurrentWeek))) '} >} LaskutettuSumma)

This obviesly didn't work and neither did the other tricks I've used so far.

Re: Limiting data using Only-function

IAMDV — Wed, 11 May 2011 12:46:23 GMT

The sample file does not have associate logic between week & month. I mean when I select the 1st month it shows (1,2,3,4,5,52,53) - We have more than 4 weeks in the month. And same applies to other months, there are few months without any weeks. So we need fix that issue before we proceed further.

Also trying to understand the actual requirement. Now we need SUM of Weekly Average for LaskutettuSumma. I mean if we select March for example and we wanted the average of LaskutettuSumma across all 4 weeks of March. Is this right? Please correct me If I am wrong.

Cheers - DV

Limiting data using Only-function

Wed, 11 May 2011 13:01:03 GMT

I think it would be easiest to use week. Sum up last 4 weeks sales (LaskutettuSumma) and just divide it with number 4. But this should work also over years, so should the week be eg YYYY-WW (2011-51)? Should some kind of calendar be created for this?

So for example at week 5 we should sum weeks 2-5 and divide that figure with 4 and so on... Here a picture of report.

Re: Limiting data using Only-function

IAMDV — Thu, 12 May 2011 08:19:31 GMT

I was assuming that you are integrating the Weekly Average score in the previous table. The idea of calculating week seems to be right (YYYY-WW). Also it will be good idea to populate a calendar table in the load script.

Cheers - DV

Re: Limiting data using Only-function

Thu, 12 May 2011 08:31:33 GMT

Could you give me a more practical answer, meaning what should I do? Sorry, but I don't know where to start. Planning seems to be easier than implementing plans.

Re: Limiting data using Only-function

Thu, 12 May 2011 08:46:48 GMT

Do you have any idea why choosing only on costcenter from sample.qvw does not affect on columns "Month last year" and "Last 12 months"? And "Last 12 months" total is ok, but there is something wrong with numbers in rows. Sum of rows doesn't match with total.

Re: Limiting data using Only-function

IAMDV — Thu, 12 May 2011 09:40:31 GMT

That is because our expressions are using {1} instead of current selection {$}.

We had used the variable to calculate the vMyYear (Max Year) with the following expression.

Max({1} YEAR) - Where represents the full set of all the records in the application

Also we had used the same logic on Months...

Sum({1<[MONTH]={$(=Only([AsOfMonth]))}, YEAR ={$(=($(vMyYear))-1)} >} LaskutettuSumma)

Hope this helps

Also I will be looking at your previous post sometime soon.

Cheers - DV

Re: Limiting data using Only-function

Thu, 12 May 2011 10:45:37 GMT

Here is a new example file for you...

Re: Limiting data using Only-function

Fri, 13 May 2011 11:25:30 GMT

Hi D V,

Are you busy? Are you able to help with this today or on Monday? Sorry, I just have to ask...

Re: Limiting data using Only-function

IAMDV — Fri, 13 May 2011 12:45:27 GMT

Very sorry! I am working from Client's place. I will look into this over weekend. I hope its okay. Also can you explain me the issue?

Thanks - DV