topic Re: Decrement Month dynamically in QlikView

Decrement Month dynamically

mansi_dv — Tue, 25 Mar 2014 09:22:00 GMT

This is with reference to

http://community.qlik.com/message/494522

Now I am able to do calculations as per the user selections. But what if I want a month which is one more or one less than the selected month?

For example, user selected may. I fetched the input using maxstring() function. Now I want the data of April (i.e. the previous month of the selected one) to do further calculations for the same selection. What should be done?

Re: Decrement Month dynamically

tresB — Tue, 25 Mar 2014 09:38:25 GMT

try like:

=sum({<[Month] = {'$(vLastMonth)'},[Year] = {'$(=maxstring({[new]}[Year]))'}>}Data)

define variable like:

vLastMonth= Month(Addmonths(Date#('1'&maxstring({[new]} [Month]) &'2000' , 'DMMMYYYY' ) ,-1) )

Re: Decrement Month dynamically

Tue, 25 Mar 2014 09:44:32 GMT

Hi,

For this Your model contains calender fields

Script to generate calender

Calender:

Load Date(TempDate,'DD/MM/YYYY') AS Date,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
NUM(Month(TempDate)) AS MonthNum,
If(Match(NUM(Month(TempDate)),1,2,3),'Q1',
If(Match(NUM(Month(TempDate)),4,5,6),'Q2',
If(Match(NUM(Month(TempDate)),7,8,9),'Q3',
If(Match(NUM(Month(TempDate)),10,11,12),'Q4')))) AS Quarter,
Day(TempDate) AS Day,

YearName(TempDate,0,4) AS FinYearName,
Year(AddMonths(TempDate,9)) AS FinYear,
Month(TempDate) AS FinMonth,
Num(Month(AddMonths(TempDate,9))) AS FinMonthNum,
If(Match(NUM(Month(AddMonths(TempDate,9))),1,2,3),'Q1',
If(Match(NUM(Month(AddMonths(TempDate,9))),4,5,6),'Q2',
If(Match(NUM(Month(AddMonths(TempDate,9))),7,8,9),'Q3',
If(Match(NUM(Month(AddMonths(TempDate,9))),10,11,12),'Q4')))) AS FinQuarter
Resident YourDataTable;

Create variables like vCurrentYear,vCurrentMonth and vPrevMonth

The followin are the expressions for those corresponding variables

=Max(FinYear)

=Max(IF(FinYear=$(vCurrentYear),FinMonthNum))

=$(vCurrentMonth)-1

Now Your Chart Expression for previous month sales would be

=Sum({<FinYear={$(vCurrentYear)},FinMonthNum={$(vPrevMonth)}>}Sales)

Re: Decrement Month dynamically

mansi_dv — Tue, 25 Mar 2014 09:45:19 GMT

Thanx Tresesco. But I am getting 0 as output.

Re: Decrement Month dynamically

tresB — Tue, 25 Mar 2014 09:47:07 GMT

Check the variable value. Is it calculating the last month properly?

Re: Decrement Month dynamically

mansi_dv — Tue, 25 Mar 2014 09:52:57 GMT

variable is also giving null data.

Re: Decrement Month dynamically

tresB — Tue, 25 Mar 2014 09:58:14 GMT

Probably your variable declaration has error. Check it, else if possible try to upload your sample qvw.