topic Re: Last 3 Month data in QlikView

Data

soha1902 — Thu, 08 Oct 2015 04:01:29 GMT

Done

Re: Last 3 Month data

saurabhwadhwa — Thu, 08 Oct 2015 04:09:26 GMT

Hi Soha,

Try this in expression.

Sum({<Date = {">=$(=Date(addmonths(Max(Date), -3))( <=$(=Date(Max(Date)))"} >} Sales)

Thanks

Re: Last 3 Month data

arulsettu — Thu, 08 Oct 2015 04:11:04 GMT

first format your date field

Date(Date#(Date,'YYYYMMDD'),'DD/MM/YYYY') as datefield,

year(datefield) as year,

month(datefield) as month

and post the excel file

Re: Last 3 Month data

robert_mika — Thu, 08 Oct 2015 04:21:36 GMT

Try like this

Table:

LOAD [Accounting Date] as Date,

[41000 Amount] as Amount

FROM

C:\Users\js46089\Desktop\data.xls

(biff, embedded labels, table is Sheet1$)

where Date(Date#([Accounting Date],'YYYYMMDD'),'DD/MM/YYYY')>=AddMonths( Date(Date#(Date,'YYYYMMDD'),'DD/MM/YYYY'),-3)

Re: Last 3 Month data

sujeetsingh — Thu, 08 Oct 2015 04:24:27 GMT

You can achieve this with multiple ways. Well for UI just use Dimension Limits

Re: Last 3 Month data

SreeniJD — Thu, 08 Oct 2015 04:32:07 GMT

As Arul suggested, first you have to change your date format...

=Date(Date#(Date,'YYYYMMDD'),'MMM YYYY') as MonthYear

and then use the below expression for previous 3 months amount

Last 3 Months

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Max(datefield), -2))<=$(=Date(Max(datefield)))’}>} Amount )

HTH

Sreeni

Re: Last 3 Month data

soha1902 — Thu, 08 Oct 2015 05:16:22 GMT

Not Working

Re: Last 3 Month data

Anonymous — Thu, 08 Oct 2015 05:42:17 GMT

Try This..

Sum({<Date={‘>=$(=MonthStart(Max(datefield), -3))<$(=MonthStart(Max(datefield)))’}>} Amount )

Re: Last 3 Month data

Thu, 08 Oct 2015 06:19:46 GMT

Script:

Date:

LOAD date,
MonthName( date) as MM_YYY,
sale
FROM
date.xlsx
(ooxml, embedded labels, table is Sheet1);

Variables:

vDate1 =date(floor(AddMonths( date,-1)))

vDate2 =date(floor(AddMonths( date,-3)))

Filter Column:

Date

Pivot Chart:

Dimension

MM_YYY

expression

Sum ({<date={'<=$(vDate1)>=$(vDate2)'}>}sale)

Re: Last 3 Month data

Thu, 08 Oct 2015 06:27:23 GMT

try to load Monthname(Date) which will give you the month name & year like this

then you can restrict the last three month in the expression using variables.