topic Re: Current Month based on Selections in QlikView

Current Month based on Selections

Karl_Hart — Thu, 24 Jul 2014 10:20:26 GMT

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.

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,

Re: Current Month based on Selections

datanibbler — Thu, 24 Jul 2014 10:28:25 GMT

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

Re: Current Month based on Selections

Karl_Hart — Thu, 24 Jul 2014 10:32:48 GMT

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)

Re: Current Month based on Selections

Thu, 24 Jul 2014 10:33:16 GMT

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

Re: Current Month based on Selections

tresB — Thu, 24 Jul 2014 10:36:18 GMT

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)

Re: Re: Current Month based on Selections

jagannalla — Thu, 24 Jul 2014 10:37:58 GMT

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

Re: Current Month based on Selections

Thu, 24 Jul 2014 10:43:44 GMT

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

];