topic Re: Previous month value in QlikView

Previous month value

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

Hello,

I have NumMonth field in my master calendar, and I am using the following code to generate NumMonth.

num(Month(TempDate)) As NumMonth

Now I want to get previous months count m based on my month selection. I am using this code.

=count({$< NumMonth={"=num(max(NumMonth))-1"}>} Reconciliation_ID)

This code is giving me current months count. if I hard code the value as

=count({$< NumMonth={5}>} Reconciliation_ID) I get the result . however it is not working with the code I am using.

Please help

Thank you,

Ashis

Re: Previous month value

Anil_Babu_Samineni — Wed, 13 Sep 2017 10:35:01 GMT

You can use this?

=count({$< NumMonth={'$(=Max(NumMonth)-1)'}>} Reconciliation_ID)

Re: Previous month value

ashis — Wed, 13 Sep 2017 10:38:03 GMT

Thank your for your reply,

It is giving me 0 as value.

Re: Previous month value

jmvilaplanap — Wed, 13 Sep 2017 10:40:52 GMT

Hi,

If you put the formula (=num(max(NumMonth))-1) in a label, what's the result? is 5?

Regards

Re: Previous month value

ashis — Wed, 13 Sep 2017 10:43:40 GMT

I am printing it in a text box

so if I put your code it is giving me 7 (numeric value ) if the month Aug(8) is selected.

Re: Previous month value

Anonymous — Wed, 13 Sep 2017 11:56:15 GMT

Please try this

=count({$< NumMonth={'$1(=Max(NumMonth)-1)'}>} Reconciliation_ID)

Regards

Tahemas Momin

Re: Previous month value

sunny_talwar — Wed, 13 Sep 2017 12:04:07 GMT

I would think this to work from Anil

=Count({$<NumMonth={'$(=Max(NumMonth)-1)'}>} Reconciliation_ID)

With a slight issue that what would you want to see when Jan is selected?

Re: Previous month value

ashis — Wed, 13 Sep 2017 12:14:02 GMT

Thanks for your reply . No it did not work.

Re: Previous month value

Anonymous — Wed, 13 Sep 2017 12:14:51 GMT

To get the previous month of current selection

=num(max({$}MonthNum))-1

to get the previous Selection

=num(max({$1}MonthNum))-1

Regards

Tahemas Momin

Re: Previous month value

Brice-SACCUCCI — Wed, 13 Sep 2017 12:19:40 GMT

Hello,

please add this field to your model : Year(Date) * 12 + Month(Date) as [Month Counter].

And then your set analysis becomes [Mounth Counter] = {'$(=MAX([Month Counter] - 1))'}.

Il you use Num Month, you will have an issue in January. The solution I propose goes to December of the previous year.

Thanks,

Brice

Re: Previous month value

ashis — Wed, 13 Sep 2017 12:23:23 GMT

Hi Sunny,

Thanks for your reply. That is very valid point. It leads me to think I should not use numeric value for month.

I tried the following code also

=count({<Month={"$(=Date(AddMonths(max(Month),-1),'MMM'))"}>} Reconciliation_ID)

I am facing one serious issue , please see my below code for master calendar.

For month calculation if I use Month(TempDate) As Month , I find it difficult to calculate such as month , previous month .

however if I use Date(MonthStart(TempDate),'MMM') as Month , I find it give correct result in set analysis.

in my current scenario that I am working on , following code Date(MonthStart(TempDate),'MMM') as Month is giving me duplicate month values such as Aug twice(since I have two years and in both the years I have few common months) , in my list box which I do not want.

I will be forever grateful if you let me know which formula to follow in master calendar.

--------------------------------------------------------

asterCalendar:
Load
TempDate AS [Date],
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Date(MonthStart(TempDate),'MMM-YY') as YearMonth,
//Date(MonthStart(TempDate),'MMM') as Month,
num(Month(TempDate)) As NumMonth,
Day(TempDate) As Day,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

Re: Previous month value

sunny_talwar — Wed, 13 Sep 2017 12:27:42 GMT

If that works for you, go for it... but I would probably use YearMonth field from your calendar to do this

Date(MonthStart(TempDate),'MMM-YY') as YearMonth,

Something like this

=Count({$<YearMonth = {"$(=Date(MonthStart(Max(YearMonth), -1), 'MMM-YY'))"}, NumMonth, Week, Year, Month, Date, Day, Quarter, WeekYear, WeekDay>} Reconciliation_ID)

Re: Previous month value

ashis — Wed, 13 Sep 2017 12:32:15 GMT

Hi Brice,

Thank you for your reply. I added your code in my script, please see the attach code.

How ever id did not work,

=count({$< MonthCounter={'$(=MAX(MonthCounter) - 1)'}>} Reconciliation_ID)

Master calendar:

-------------------

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
min(Date) as minDate,
max(Date) as maxDate
Resident ExceptionDataFeedRun;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load
TempDate AS [Date],
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Date(MonthStart(TempDate),'MMM-YY') as YearMonth,
//Date(MonthStart(TempDate),'MMM') as Month,
Year(TempDate) * 12 + Month(TempDate) as [MonthCounter],
num(Month(TempDate)) As NumMonth,
Day(TempDate) As Day,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

Re: Previous month value

Brice-SACCUCCI — Wed, 13 Sep 2017 12:35:41 GMT

I've forgotten a ')', sorry! I've edited my first answer

Re: Previous month value

ashis — Thu, 14 Sep 2017 04:47:28 GMT

Hi Brice,

Please see my code, I have added your code in my Master Calender. then I used [Month Counter] in my set analysis

=count({$< [Month Counter]={'$(=MAX([Month Counter] - 1))'}>} Reconciliation_ID) or

=count({$< [Month Counter]={'$(=MAX([Month Counter]-1))'}>} Reconciliation_ID) , however it is returning 0 value. Please guide.

MasterCalendar:
Load
TempDate AS [Date],
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Date(MonthStart(TempDate),'MMM-YY') as YearMonth,
//Date(MonthStart(TempDate),'MMM') as Month,
Year(TempDate) * 12 + Month(TempDate) as [Month Counter],
num(Month(TempDate)) As NumMonth,
Day(TempDate) As Day,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;

Re: Previous month value

bhadraka1234 — Thu, 14 Sep 2017 05:41:23 GMT

Count({$<NumMonth= {"<=$(=max(NumMonth)) >$(=max(NumMonth)-(5))"}>}DISTINCT NumMonth)

Re: Previous month value

ashis — Thu, 14 Sep 2017 08:02:13 GMT

Hi Brice,

Thank you for your guidance, it worked for me if I am calculating using variable.like the following script

vPreviousMonthCounter=max([Month Counter]) -1

=count({1<[Month Counter]={'$(vPreviousMonthCounter)'}>} Reconciliation_ID)

Thank you,