Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Retrieving previous months data based on selection

Hi All,

I got stuck in a situation where the client has a MTD( Which is not exactly a MTD calculation)

Where when the user selects a month the MTD value has to be calculated not form first of month to selected date but it has to show previous month data irrespective of what ever date is selected (To avoid the complexity and confusion we are just using month instead Date)

Please help me on this.

Thanks

Kumar

dvqlikviewcheenujanakiramgwassenaarjaganApp DevelopmentScripting

can you guys please guide me on this please

1 Solution

Accepted Solutions
MVP
MVP

Re: Retrieving previous months data based on selection

Date field in your sample is a text field. Reload the application converting the date field into a proper date field (numeric at the backend). You may try like:

Load

           Date(Date#(period, 'MM/DD/YYYY')) as Date,

           Month(Date#(period, 'MM/DD/YYYY')) as Month

...;

Then at the front end try like:

Sum({<Date={">=MonthStart(Max(Date),-1) <=MonthEnd(Max(Date),-1)"}>}spend)

8 Replies
qumniusomnius
New Contributor III

Re: Retrieving previous months data based on selection

something like this:

sum({<Date = { '>=$(monthstart(Addmonths(Max(Date),-1))<=monthend(Addmonths(Max(Date),-1)) )'} >} Value)

MVP
MVP

Re: Retrieving previous months data based on selection

It would majorly depend on your Date/Month field data type. Try to share your sample qvw.

Not applicable

Re: Retrieving previous months data based on selection

Attached the QVW to the orginal post

MVP
MVP

Re: Retrieving previous months data based on selection

Date field in your sample is a text field. Reload the application converting the date field into a proper date field (numeric at the backend). You may try like:

Load

           Date(Date#(period, 'MM/DD/YYYY')) as Date,

           Month(Date#(period, 'MM/DD/YYYY')) as Month

...;

Then at the front end try like:

Sum({<Date={">=MonthStart(Max(Date),-1) <=MonthEnd(Max(Date),-1)"}>}spend)

Not applicable

Re: Retrieving previous months data based on selection

are you speaking about the data field in the Master Calendar???

MVP
MVP

Re: Retrieving previous months data based on selection

I see only one date field called period in your sample qvw.

MVP
MVP

Re: Retrieving previous months data based on selection

Hi,

Try like this

Change your script like below

LOAD [Supplier Name],

     [Commodity Manager],

     Plant,

     Date(period) AS period,

     spend,

     PPV

FROM

Sample.xlsx

(ooxml, embedded labels, table is Sheet1);

Expression:

sum({<period = {">=$(monthstart(Addmonths(Max(period),-1)))<=monthend(Addmonths(Max(period),-1)) )"} >} spend)

Not applicable

Re: Retrieving previous months data based on selection

Did that...I believe the issue is fixed now..... Thanks every one for the help.

Community Browser