topic show current and previous 2 months. in QlikView

show current and previous 2 months.

Thu, 16 Aug 2012 17:12:38 GMT

Hi,

I am trying to create a bar graph that shows the count(month) but does not show any data from two months prior to the current date. I have my dimension as Month and Expression as count(month). That shows the graph with the correct numbers but i would like to only show the previous two month from the current date. I tried to create another expressions Sum({<Month={"$(=Max(Month)-2)"}>}[Month] but I think its wrong.

Any help is appreciated.

Thank you

Re: show current and previous 2 months.

Jason_Michaelides — Thu, 16 Aug 2012 17:24:07 GMT

Almost right, I think:

Count({<Month={">=$(=Max(Month)-2)"}>}[Month] )

Or better still, if you're on v11 use dimension limits.

Hope this helps,

Jason

Re: show current and previous 2 months.

Thu, 16 Aug 2012 19:12:42 GMT

I am still seeing all the months listed in the graph. Should the expression show both items? Count(Month) and

Count({<Month={">=$(=Max(Month)-2)"}>}[Month] )? I tried with one and both and no changes seem to occur. I have the dimension still with just month. Below is the data i get when i export the graph to excel.

Month	Count({<Month={">=-"}>}[Month] )	count (Month)
1899-12	0	1172
2011-08	0	4
2011-09	0	5
2012-01	0	48
2012-04	0	26
2012-05	0	21
2012-06	0	8
2012-07	0	14
2012-08	0	5
2012-10	0	19
2012-11	0	77

Re: show current and previous 2 months.

Thu, 16 Aug 2012 20:49:07 GMT

A few suggestions:

1) You'd better use month-year instead of simply month, othervise you will face problems crossing year borders.

2) Use AddMonths function to get earlier/later months.

E.g.

Count({<MonthYear={">=$(=AddMonths(Max(MonthYear),-2))"}>} [MonthYear] )

Re: show current and previous 2 months.

Jason_Michaelides — Thu, 16 Aug 2012 21:02:01 GMT

Good advice from Dmitry, and also it looks like your "Month" field is not actually a numeric date field. Before AddMonths() will work you need to ensure "Month" is a date field.

Re: show current and previous 2 months.

Mon, 20 Aug 2012 16:17:47 GMT

Thank you, Very helpful.

Nicholas

Re: show current and previous 2 months.

Thu, 27 Sep 2012 14:13:24 GMT

I thought i had this down but seemed to have lost it. My graph just shows a blank. I am using QV 10, so i do not have the dimensions finction available. When I use your recommended expression

Count({<MonthYear={">=$(=AddMonths(Max(MonthYear),-2))"}>} [MonthYear] ) I am just getting a blank. My Dimension has the month listed in it.

Sorry and thanks again.

Re: show current and previous 2 months.

MayilVahanan — Thu, 27 Sep 2012 18:43:21 GMT

Can you able to say the MonthYear format?

Because Addmonths() gives like 11/11/2012 ..

MonthYear is also same format? i.e DD/MM/YYYY

Re: show current and previous 2 months.

Thu, 27 Sep 2012 21:10:07 GMT

Im not sure I understand what you are looking for exactly. I have this in my script to make sure it is coming through as a date format.

Date#(CompDate,'DD/MM/YYYY') as NumCompdate,

Re: show current and previous 2 months.

MayilVahanan — Thu, 27 Sep 2012 21:11:52 GMT

MonthYear format?? Can you give a sample value for MonthYear?

Re: show current and previous 2 months.

Thu, 27 Sep 2012 21:29:35 GMT

Topic Area	NumCompDate	ows_ComDate	CompDateMonth
#Agri Commodity Definition	2011-09-12 00:00:00	9/12/2011	Sep
#Agri Swaps	2012-10-12 00:00:00	10/12/2012	Oct
#Amend to July 14, 2011 Order for Swap Regulation	2011-12-23 00:00:00	12/23/2011	Dec
#Amend to CPO and CTA Regulations Resulting from DF Act	2012-11-05 00:00:00	11/5/2012	Nov
#Amend to CPO and CTA Regulations Resulting from DF Act
#Ben Ownership Reporting Requirements and Security-Based Swaps	2011-07-16 00:00:00	7/16/2011	Jul
#Business Affiliate Marketing and Disposal of Consumer Information Rules	2012-09-21 00:00:00	9/21/2012	Sep
#Business Affiliate Marketing and Disposal of Consumer Information Rules
#Clearing Exemption for Swaps Between Certain Affiliated Entities
#Commodity Options	2012-10-12 00:00:00	10/12/2012	Oct

Re: show current and previous 2 months.

MayilVahanan — Thu, 27 Sep 2012 21:43:25 GMT

Try like this

=Count({<DateField={">=$(=AddMonths(Max(DateField),-2))"}>} CountField )

Its takes max(DateField) for example 05/11/2012 format DD/MM/YYYY

Calculate dated from 05/09/2012 to 05/11/2012 and count the values

Suppose want to calcualte from 01/09/2012 to 05/11/2012 , use MonthStart(AddMonths(Max(DateField),-2))

Hope that helps

Re: show current and previous 2 months.

Tue, 02 Oct 2012 20:26:42 GMT

Hi,

I tried the expression but again nothing. Keep getting an error message--> "no data to display."

When I use this expression :

Count({<ComplianceDateMonth={">=$(=AddMonths(Max(ComplianceDateMonth),-2))"}>} [ComplianceDateMonth]

)

I get a bar graph but with every date from sep-2010 to mar-2014 listed. The dimension I use is a datemonth field (shows MM-YYYY). It still is not showing the current date and past 2 months.

Re: show current and previous 2 months.

MayilVahanan — Tue, 02 Oct 2012 20:47:46 GMT

Instead of using

[ComplianceDateMonth] field , can you able to use Datefield(i.e. not the monthYear field but date field in the expression)

Hope that helps

Re: show current and previous 2 months.

Tue, 02 Oct 2012 21:37:46 GMT

Hi,

I changed it to:

Count

({<ows_ComplianceDate={">=$(=AddMonths(Max(ows_ComplianceDate),-2))"}>} [ows_ComplianceDate])

My expression is DateTest which = Date(ows_ComplianceDate,'MMM-YYYY') as DateTest

Thank you for your help.

Re: show current and previous 2 months.

GabrielAraya — Tue, 02 Oct 2012 21:38:32 GMT

Hi .. Look this example ...

I hope it can be useful for you

Gabriel

Re: show current and previous 2 months.

Wed, 03 Oct 2012 16:48:05 GMT

Thank you. I have figured it out.

Also thank you Mayil Vahanan. I would not have understood this without your imput.