Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
RohanPR
Contributor
Contributor

Calculate Previous Month Value based on Filter Selection

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

2 Solutions

Accepted Solutions
sunny_talwar

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)

View solution in original post

sunny_talwar

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

View solution in original post

13 Replies
sunny_talwar

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

RohanPR
Contributor
Contributor
Author

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

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
Contributor
Contributor
Author

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

Hi,

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

 

Regards,

Rohan

RohanPR
Contributor
Contributor
Author

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
Contributor
Contributor
Author

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

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