Limiting data using Only-function

[#1]

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.

[IAMDV]

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

[IAMDV]

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

Cheers - DV

[#4]

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

[#5]

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

[IAMDV · Accepted solution]

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

[#7]

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?

[IAMDV]

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

[#9]

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

[IAMDV]

Excellent!