Current Month based on Selections

[Karl_Hart]

All,

I've tried to find the answer but nothing seems to be working:

I want to be able to show the current month, previous month and YTD based on selections made.

eg

Month     Sales

Jan          10

Feb          20

Mar          15

Apr          32

May          -

Jun           -

Jul           -

Aug           -

Sep          -

Oct          -

Nov          -

Dec          -

When I select Jan, Feb, Mar I want three different values returned:-

Current Month = 15

Previous Month = 20

YTD = 45

I've tried a number of different set analysis type solutions based on these forums but nothing is giving me the right answers. I thought it should be pretty straight-forward.

***PS I am still on a personal edition so I'm unable to open any qvw files so could you post the code directly on here please***

Thanks,

[datanibbler]

Hi Karl,

well, YTD should be simple - you only specify the current year in the set_expression, like

>> sum({1<Jahr = {$(=Year(Today()))}>} sales) <<

The previous month - try that out in a checkbox, I guess it would be easier using the DUAL() function, but I have no experience with that - I always do like

>> sum({1<Jahr = {$(=Year(Today())), Monat = {$(=Month(Monthstart(Today(), -1)))}}>} sales) <<

HTH

Best regards,

DataNibbler

[Karl_Hart]

Thanks, does the today() function ignore the current selections?

So if look at the data today and use your suggestion will it return everything from Jan - Jul (given that we are in July) because if so I dont want to use that function as I only want it to return results based on my selections (in this case Jan - Mar)

[#4]

Hi Karl,

Firstly you just need to select a single month( e.g March) to get the values as below:

Current Month = 15

Previous Month = 20

YTD = 45

Secondly do you have a Master Calendar in your script?

It would be better if you could attach your QVW so that we may analyze and give you the code.

Thanks

Sabal

[tresesco · Accepted solution]

See attached qvw.

='Current Month Sales :'&Sum({<Date=,Month={"$(=Month(Max(Date)))"}>}Sales)

='Previous Month Sales :'&Sum({<Date=,Month={"$(=Month(AddMonths(Max(Date),-1)))"}>}Sales)

='YTD :'&Sum({<Month=,Date={">=$(=YearStart(Max(Date)))<=$(=Max(Date))"}>}Sales)

[jagannalla]

Hi,

Try this

LOAD *,Dual(Month,RowNo())*1 as MonthNo;

LOAD * INLINE [

    Month, Sales

    Jan, 10

    Feb, 20

    Mar,15

    Apr,32

    May,

    Jun,

];

In different text box, exp are

Current Month = 15 -> =Sum({<MonthNo={$(=Max(MonthNo))}>}Sales)

Previous Month = 20 ->=Sum({<MonthNo={$(=Max(MonthNo)-1)}>}Sales)

YTD = 45 -> =Sum(Sales)

Cheers!!

Jagan

[#7]

Karl you will probably need the below from tresesco's qvw if you are unable to open due to running personal.

load *,

    Month(Date) as Month;

load * Inline [

Managers, Date, Sales

A, 1-1-2014, 200

A, 1-2-2014, 300

B, 1-3-2014, 500

B, 1-4-2014, 100

B, 1-5-2014, 500

C, 1-6-2014, 10

C, 1-7-2014, 20

];