topic Re: Retrieving previous months data based on selection in QlikView

Retrieving previous months data based on selection

Thu, 30 Jul 2015 09:20:50 GMT

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

Re: Retrieving previous months data based on selection

qumniusomnius — Thu, 30 Jul 2015 09:31:20 GMT

something like this:

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

Re: Retrieving previous months data based on selection

tresB — Thu, 30 Jul 2015 09:34:39 GMT

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

Re: Retrieving previous months data based on selection

Thu, 30 Jul 2015 09:41:10 GMT

Attached the QVW to the orginal post

Re: Retrieving previous months data based on selection

tresB — Thu, 30 Jul 2015 09:54:47 GMT

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)

Re: Retrieving previous months data based on selection

Thu, 30 Jul 2015 09:58:27 GMT

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

Re: Retrieving previous months data based on selection

tresB — Thu, 30 Jul 2015 10:03:53 GMT

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

Re: Retrieving previous months data based on selection

jagan — Thu, 30 Jul 2015 10:14:48 GMT

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)

Re: Retrieving previous months data based on selection

Thu, 30 Jul 2015 10:18:47 GMT

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