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

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

apoorvasd — Tue, 18 Jul 2017 09:56:20 GMT

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.

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

sunny_talwar — Tue, 18 Jul 2017 12:12:15 GMT

May be something like this

If(Sum({<Date={">=$(=YearStart(Today()))<$(=Max(MonthStart(Today())))"}, Year, Month>} [Actual Planned Hours) = 0, Avg({<Date={">=$(=YearStart(Today()))<$(=Max(MonthStart(Today())))"}, Year, Month>} [Actuals]), Sum({<Date={">=$(=YearStart(Today()))<$(=Max(MonthStart(Today())))"}, Year, Month>} [Act OEE] * [Actual Planned Hours])/Sum({<Date={">=$(=YearStart(Today()))<$(=Max(MonthStart(Today())))"}, Year, Month>} [Actual Planned Hours]))

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

apoorvasd — Wed, 19 Jul 2017 10:13:52 GMT

HI Sunny,

Thanks for the reply.

I tried your expression but it displays lower values than expected!

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

sunny_talwar — Wed, 19 Jul 2017 11:00:24 GMT

Won't know unless you can share a sample with the number you expect to see

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

mangalsk — Wed, 19 Jul 2017 11:01:06 GMT

Hello ,

You are taking dates from Jan to max date then no need to explicitly mention month because it will take all months from dates by this expression only without need of mentioning months

if(sum({<Date={">=$(=YearStart(Date))<=$(=AddMoths(Max(Date),-1))"},Year={2017},,Date=>}[Actual Planned Hours) = 0, avg({<Date={">=$(=YearStart(Date))<=$(=AddMoths(Max(Date),-1))"},Year={2017},,Date=>}[Actuals]),

sum({<Date={">=$(=YearStart(Date))<=$(=AddMoths(Max(Date),-1))"},Year={2017},Date=>}[Act OEE]*[Actual Planned Hours])/sum({<Date={">=$(=YearStart(Date))<=$(=AddMoths(Max(Date),-1))"},Year={2017},,Date=>}[Actual Planned Hours]))

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

balar025 — Wed, 19 Jul 2017 11:17:29 GMT

Hi,

You can try this thing in script using AsOfMonth calendar.

Regards,

Ravi balar

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

apoorvasd — Wed, 19 Jul 2017 11:56:37 GMT

I'm sorry, data is confidential. I can't share a sample.

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

apoorvasd — Wed, 19 Jul 2017 11:58:29 GMT

Hi Mangal,

Even I thought the same. But there is still some difference that I see when I add month names into the expression and when I don't.

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

apoorvasd — Wed, 19 Jul 2017 11:58:59 GMT

I will try this and check.

Thank you.

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

mangalsk — Wed, 19 Jul 2017 12:11:57 GMT

You try it will work sure else share dummy file

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

sunny_talwar — Wed, 19 Jul 2017 12:12:42 GMT

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...

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

apoorvasd — Fri, 21 Jul 2017 10:02:47 GMT

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.

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

apoorvasd — Fri, 21 Jul 2017 10:04:21 GMT

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

Thank you.

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

apoorvasd — Fri, 21 Jul 2017 10:05:19 GMT

Sorry, here it is.

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

mangalsk — Fri, 21 Jul 2017 11:39:39 GMT

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

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

apoorvasd — Fri, 21 Jul 2017 11:52:58 GMT

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.

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

mangalsk — Fri, 21 Jul 2017 12:20:14 GMT

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

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

apoorvasd — Mon, 24 Jul 2017 04:46:41 GMT

Hi Mangal,

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

Thank you.

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

mangalsk — Mon, 24 Jul 2017 04:59:35 GMT

Have you checked attached new qvw, it is matching exact

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

apoorvasd — Mon, 24 Jul 2017 05:05:41 GMT

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