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

[apoorvasd]

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.

[sunny_talwar]

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]))

[apoorvasd]

HI Sunny,

Thanks for the reply.

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

[sunny_talwar]

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

[mangalsk]

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]))

[balar025]

Hi,

You can try this thing in script using AsOfMonth calendar.

Regards,

Ravi balar

[apoorvasd]

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

[apoorvasd]

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.

[apoorvasd]

I will try this and check.

Thank you.

[mangalsk]

You try it will work sure else share dummy file