topic Re: 6 months in QlikView

6 months

raadwiptec — Wed, 25 Nov 2020 16:16:04 GMT

I need an expression to just display the last 6 months data only on my staright table..I have a Month and year column

Re: 6 months

Anonymous — Wed, 24 Aug 2016 08:29:57 GMT

try this

Month Need to be your monthfield

Monthnum contains month number, if not available use other date functions

sum({<Month=,Monthnum={'>=$(max(Monthnum)-1)'}>}

alternate:

sum({<Month=,Datefield={'>=$(monthstart(addmonths(max(Datefield)-6)))'}>}

Re: 6 months

vinieme12 — Wed, 24 Aug 2016 08:31:41 GMT

SUM( {< Year = {$(=Max(Year))}

, Month = {">$(=Max(Month)-6)<=$(=Max(Month))"}

>} YOURFIELD )

Re: 6 months

Anonymous — Wed, 24 Aug 2016 08:32:51 GMT

Hi try this

*Provided : Your Month is in 'MMM' format (JAN,FEB,...etc.,) and aggregating on sales ...

Sum({$<[Month]={">=$(=Date(AddMonths(Max(Month), -6), 'MMM'))<=$(=Date(Max(Month),'MMM'))"}>}[Sales])

Re: 6 months

maxgro — Wed, 24 Aug 2016 08:34:51 GMT

look at the attachment

Re: 6 months

jmvilaplanap — Wed, 24 Aug 2016 08:36:34 GMT

It depends if you want to show the last six months since today or the last six months since the last date selected.

(Updating the previous response)

SUM( {< Year = {$(=Year(Today()))}

, Month = {">$(=Month(Today())-6)<=$(=Month(Today())"}

>} YOURFIELD )

Re: 6 months

raadwiptec — Wed, 24 Aug 2016 11:37:51 GMT

hi jose

i need

last six months since today

for ex: August ---> back to Feb

Jan should not be displayed.

Re: 6 months

jmvilaplanap — Wed, 24 Aug 2016 11:43:28 GMT

Then you can use my proposal:

SUM( {< Year = {$(=Year(Today()))}

, Month = {">$(=Month(Today())-6)<=$(=Month(Today())"}

>} YOURFIELD )

But be careful with other date fields when you filter in the dashboard, you must put it in the set analisys with blank filter, for example {< Year = {$(=Year(Today()))}, Date=>}.

Re: 6 months

raadwiptec — Thu, 25 Aug 2016 12:39:28 GMT

hi jose this is not working ... i need generic calculation condition in the general tab of the chart saying

year is max of year and month is 6 months data only

Re: 6 months

jmvilaplanap — Thu, 25 Aug 2016 12:55:21 GMT

6 months data only means for example if today is 08/25/2016, you must show data from 02/25/2016 or from 02/01/2016?

In the fisrt case you mus use date instead year and month.

Re: 6 months

raadwiptec — Thu, 25 Aug 2016 13:04:54 GMT

yes i need with date ..can ypu give me the full expression for date.. as i dont want to hard code .. it should be dynamic..today - 6 months

{< Year = {$(=Year(Today()))}, Date=>}.

Re: 6 months

raadwiptec — Thu, 25 Aug 2016 13:14:46 GMT

and secondly should date be one of the dimensions or expressions in the chart?

Re: 6 months

ramchalla — Thu, 25 Aug 2016 14:05:17 GMT

Hi Raadwiptec,

Try the below expression.

=SUM({<Date = {">=$(=Addmonths(Max(Date),-6))<=$(=Max(Date))"}>} Sales)

Re: 6 months

jmvilaplanap — Fri, 26 Aug 2016 06:42:18 GMT

Try with this:

{< Year = {$(=Year(Today()))}, Date={">=$(=addmonths(today(),-6))"}>}

And not is necesary that the day (or date) be on the chart (as expression or dimension)