Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Limiting data using Only-function

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.

21 Replies
Not applicable
Author

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.

IAMDV
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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.

Weekly avg.png

IAMDV
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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.

Not applicable
Author

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.

IAMDV
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

Here is a new example file for you...

Not applicable
Author

Hi D V,

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

IAMDV
Luminary Alumni
Luminary Alumni

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