Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
RohanPR
New 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

Re: Calculate Previous Month Value based on Filter Selection

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

Highlighted

Re: Calculate Previous Month Value based on Filter Selection

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

View solution in original post

12 Replies

Re: Calculate Previous Month Value based on Filter Selection

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

RohanPR
New Contributor

Re: Calculate Previous Month Value based on Filter Selection

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

Re: Calculate Previous Month Value based on Filter Selection

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

RohanPR
New Contributor

Re: Calculate Previous Month Value based on Filter Selection

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

Re: Calculate Previous Month Value based on Filter Selection

Hi,

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

 

Regards,

Rohan

RohanPR
New Contributor

Re: Calculate Previous Month Value based on Filter Selection

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

Re: Calculate Previous Month Value based on Filter Selection


@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
New Contributor

Re: Calculate Previous Month Value based on Filter Selection

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

Highlighted

Re: Calculate Previous Month Value based on Filter Selection

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

View solution in original post