Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
something like this:
sum({<Date = { '>=$(monthstart(Addmonths(Max(Date),-1))<=monthend(Addmonths(Max(Date),-1)) )'} >} Value)
It would majorly depend on your Date/Month field data type. Try to share your sample qvw.
Attached the QVW to the orginal post
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)
are you speaking about the data field in the Master Calendar???
I see only one date field called period in your sample qvw.
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)
Did that...I believe the issue is fixed now..... Thanks every one for the help.