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: 
apoorvasd
Creator II
Creator II

How to display data from Jan until previous month in QlikView?

Hello everyone,

I have a table chart in which data has to be displayed from Jan of that year until previous month. What I mean by this is, I have an expression something like below:

if(sum({<Date={">=$(=YearStart(Date))<=$(=Max(Date))"},Year={2017},Month={"Jan","Feb","Mar","Apr","May","Jun"},Date=>}[Actual Planned Hours) = 0, avg({<Date={">=$(=YearStart(Date))<=$(=Max(Date))"},Year={2017},Month={"Jan","Feb","Mar","Apr","May","Jun"},Date=>}[Actuals]),

sum({<Date={">=$(=YearStart(Date))<=$(=Max(Date))"},Year={2017},Month={"Jan","Feb","Mar","Apr","May","Jun"},Date=>}[Act OEE]*[Actual Planned Hours])/sum({<Date={">=$(=YearStart(Date))<=$(=Max(Date))"},Year={2017},Month={"Jan","Feb","Mar","Apr","May","Jun"},Date=>}[Actual Planned Hours]))

the problem is, 1st of every month I manually enter previous month's name in the expression. Is there a way to automate this expression so that previous month is automatically picked up and the data displayed is sum until previous month?

Any suggestion?

Thank you.

21 Replies
sunny_talwar

This thread might help you address the confidentiality concerns

Preparing examples for Upload - Reduction and Data Scrambling

Or mock up some data to show your issue...

apoorvasd
Creator II
Creator II
Author

Attached is the sample application. I see a very small difference here and the data here is shown for just one plant. This is the same case with other plants as well. Let me know if this sample is fine or you need something more.

Thank you.

apoorvasd
Creator II
Creator II
Author

Attached is the sample app. Let me know if this fine or you need something more.

Thank you.

apoorvasd
Creator II
Creator II
Author

Sorry, here it is.

mangalsk
Creator III
Creator III

PFA , i have changed it and now it is giving correct output

apoorvasd
Creator II
Creator II
Author

Hi,

Yes, it displays correct values now. But the thing is, I want these values to be displayed even when we don't select any values in Year and month fields (something like YTD). Selections shouldn't matter, this is a constant value until previous month.

Any help?

Thank you.

mangalsk
Creator III
Creator III

OK by default you want to consider for previous of today month since you have future data also, for this find attached for both

apoorvasd
Creator II
Creator II
Author

Hi Mangal,

Values in the app still do not match with expected values right?!

Thank you.

mangalsk
Creator III
Creator III

Have you checked attached new qvw, it is matching exact

apoorvasd
Creator II
Creator II
Author

Yes, I checked the new file itself. I still see the difference as shown in the screenshot attached in my previous comment!