Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming Aug. 9: New Simplified Authoring for Qlik Sense SaaS – For Details, CLICK HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Karl_Hart
Creator
Creator

Current Month based on Selections

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,

1 Solution

Accepted Solutions
tresesco
MVP
MVP


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)

View solution in original post

6 Replies
datanibbler
Champion
Champion

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
Creator
Creator
Author

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)

Not applicable

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
MVP
MVP


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
Partner - Specialist III
Partner - Specialist III

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

Not applicable

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

];