Calculate Previous Month Value based on Filter Selection

[RohanPR]

Hi,

I know this topic has been discussed many time and I have gone through each and every post. However, I am unable to get previous month value (count of account number) for the current year in my data set. The sample of my data set is given below

Month_Production , Acc_No,  Category

Jan -18 , 123456,E

Jan-18, 234567,E

Jan-18, 123567,E

Feb-18, 246801, E

Feb-18, 357680,E

Mar-18,786502,E

Jan-18, 234590, P

Jan-18, 349086,P

Jan-18, 124509,P

Feb-18, 567890,P

Feb-18, 788090,P

Feb-18, 890877,P

E.g. 1  If I select category E and Month_Prod as Feb-18 from the multi drop down filter  then the Prev month count for account number should display 3

E.g. 2 If I select category E and Month_Prod as Mar-18 from the multi drop down filter then the Prev month count for account number should display 2.  

PS: I wan to display the number in the text box in front end. I used set analysis but didn't end up with right answer. Looking forward to a quick TAT. Thanks in advance

Thanks and Regards,

Rohan

[sunny_talwar]

First thing first, how exactly do you create Month_Production field in the script? Is it created using Date() function?

[RohanPR]

I am not creating the month_production field in my script. It is coming from the raw file.  It is not created using date function.

It indicates the first day of the month. e..g 1/1/2018, 2/1/2018,3/1/2018 etc. It is in 'MM/DD/YYYY' format and is represented as Jan-18, Feb -18 etc

[sunny_talwar · Accepted solution]

In that case, it might not be read as a date field, but in order to make it work, get it to be read as a date field.

Date(MonthStart(Date#(Month_Production, 'MMM-YY')), 'MMM-YY') as Month_Production

now use this for your last month's expression

Count({<Month_Production = {"$(=Date(MonthStart(Max(Month_Production), -1), 'MMM-YY'))"}>} Acc_No)

[RohanPR]

OK thanks for the reply let me try it and get back to you on the same

[RohanPR]

Hi,

I tried the calculation you have me. However, the first calculation evaluates to blank. Any idea why this is happening?

Regards,

Rohan

[RohanPR]

In the inline load statement i tried and it worked. However, in my data set it didn't work. When I dug deep I found out that the month_production is given as 1/1/2018,2/1/2018,3/1/2018 etc in MM/DD/YYYY format. So when I tried applying your formula it failed and gave me a blank value. I removed the date function you had used and applied the formula. Is there any other way I can calculate or should I convert it into Jan-18,Feb-18 etc and then apply your formula

Regards,

Rohan

[sunny_talwar]

---

@RohanPR wrote:

Hi,

I tried the calculation you have me. However, the first calculation evaluates to blank. Any idea why this is happening?

---

First calculation? Which 1st calculation are you talking about?

[RohanPR]

Date(MonthStart(Date#(Month_Production, 'MMM-YY')), 'MMM-YY') as Month_Production

This is the first calculation I was talking about. However, I kind of tweaked your date format to suit my data and it is working now. Thanks a lot. Appreciate it 🙂

Count({<Month_Production = {"$(=Date(MonthStart(Max(Month_Production), -1), 'MMM YY'))"}>} Acc_No)

This is the final formula i used finally

Regards,

Rohan

[sunny_talwar · Accepted solution]

Awesome, I am glad you were able to figure it out.