How to combine sum & monthsstart

[#1]

Hi,

I'm the beginner and need your help.

I would show in the one chart sales for current and previous months and don't know how to prepare script.

(Sum({$}[Sales 1])) - I used that one for present selected month.

What script should I use for presenting data tied with previous than selected month. I tried to use "MonthsStart(1,Period,-1)" but can not combine it with one above.

Regards,

Arek

[IAMDV]

Arek,

How about using Set Analysis to get the previous month sales numbers:

sum( {$<MyMonth= {$(=Only(MyMonth)-1)}>} Sales)

I'm attaching a simple Set Analysis tutorial for your reference.

Good luck.

Cheers,

DV

[#3]

Deepak,

Thanks for your quick reply.

Using the code you already posted I get the same data (selected month) in both bars Actual & Previous...

Regards,

Arek

[IAMDV]

Arek - I should have been more specific. That was just an idea on how you can use the same in Set Analysis. Please can you post the same QVW file? I'm sure it's something to do with the date formats. I'll work with your example if you can provide...

Cheers - DV

[IAMDV]

Above file is corrupted. Not sure if you can open it.

Thanks,

DV

[#6]

Could you open that one?

Regards,
Arek

[IAMDV · Accepted solution]

Arek- Here you go. You will see previous month's data only when there is selection on Month Field. If you have multiple Year's data then you need to improve this expression to handle the January to December conversion for Previous Month.

This is the expression :

SUM({<Month = {$(=Month(MakeDate(Year,Num(Month)-1)))}>} Revenue)

And I'm also attaching the QVW file for your reference.

I hope this makes sense.

Cheers,

DV

www.QlikShare.com

[#8]

Perfect solution:)

Thanks a lot