topic Creating a Dynamic Expression using Set Analysis in QlikView

Creating a Dynamic Expression using Set Analysis

Mon, 15 Mar 2010 15:09:06 GMT

I would like to be able to select Year and Current Month and have the results returned give me current year and current month +2. So, if I select 2009 and select January...I want the Dollars returned to equal January, February and March (45,000 total). I'm sure this is simple, but I am new to Qlikview and am learning how to create these expressions. Thank you!

Total Dollars
	2009	2010
January	10,000	30000
February	15,000	20000
March	20,000	15000
April	50,000	25000
May	10,000	30000
June	20,000	20000
July	25,000	30000
August	20,000	20000
September	50,000	15000
October	10,000	25000
November	20,000	30000
December	25,000	20000

Creating a Dynamic Expression using Set Analysis

Mon, 15 Mar 2010 16:04:13 GMT

Hi Palommel,

Here you are a possible solution.

I hope this would help to you.

Best regards.

Creating a Dynamic Expression using Set Analysis

Fri, 19 Mar 2010 15:02:55 GMT

Thanks Miguel. This was helpful, but I need a little more guidance. Thanks!

Year	Period	Year/Period	Amount
2008	01	200801	40	Selections: Year Period = 200902
2008	02	200802	50	Results should be: 70
2008	03	200803	10
2008	04	200804	20	We want the expression to bring back total of 3 period (current plus prior 2)
2008	05	200805	30
2008	06	200806	40	The formula below worked, however we are
2009	01	200901	10	struggling to get the system to cross over years.
2009	02	200902	20	Instead of knowing that the Periods go from 1 - 13, the system continues counting
2009	03	200903	30	(I,e. 200902, 200901, 200900, 200899, etc.)
2009	04	200904	40	We need it to recognize 200902, 200901, 200813, 200812, etc…
2009	05	200905	50
2009	06	200906	10	=Sum({< Date={'>=$(=Max(Date)) <=$(=AddMonths(Max(Date), +2))'},
2009	07	200907	20	Month = >} Amount)
2009	08	200908	30
2009	09	200909	40	Any suggestions????
2009	10	200910	50
2009	11	200911	10
2009	12	200912	20
2009	13	200913	30

Creating a Dynamic Expression using Set Analysis

Fri, 19 Mar 2010 15:05:35 GMT

ignore chart above, it is incorrect. Please use this information...thank you!

Year	Period	Year/Period	Amount
2008	08	200808	40	Selections: Year Period = 200902
2008	09	200809	50	Results should be: 70
2008	10	200810	10
2008	11	200811	20	We want the expression to bring back total of 3 period (current plus prior 2)
2008	12	200812	30
2008	13	200813	40	The formula below worked, however we are
2009	01	200901	10	struggling to get the system to cross over years.
2009	02	200902	20	Instead of knowing that the Periods go from 1 - 13, the system continues counting
2009	03	200903	30	(I,e. 200902, 200901, 200900, 200899, etc.)
2009	04	200904	40	We need it to recognize 200902, 200901, 200813, 200812, etc…
2009	05	200905	50
2009	06	200906	10	=Sum({< Date={'>=$(=Max(Date)) <=$(=AddMonths(Max(Date), +2))'},
2009	07	200907	20	Month = >} Amount)
2009	08	200908	30
2009	09	200909	40	Any suggestions????
2009	10	200910	50
2009	11	200911	10
2009	12	200912	20
2009	13	200913	30

Creating a Dynamic Expression using Set Analysis

Fri, 26 Mar 2010 08:32:03 GMT

Hi Palommel,

Here you are a possible solution, I hope this helps you.

Best regards.