Retrieving previous months data based on selection

[#1]

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

dvqlikview‌cheenujanakiram‌gwassenaar‌jagan‌App Development‌Scripting‌

can you guys please guide me on this please

[qumniusomnius]

something like this:

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

[tresesco]

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

[#4]

Attached the QVW to the orginal post

[tresesco · Accepted solution]

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)

[#6]

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

[tresesco]

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

[jagan]

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)

[#9]

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