Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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

1 Solution

Accepted Solutions
mangalsk
Contributor III

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

Sorry , i saved without 1 change, please find attached

21 Replies
MVP
MVP

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

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

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

HI Sunny,

Thanks for the reply.

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

MVP
MVP

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

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

mangalsk
Contributor III

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

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

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

Hi,

You can try this thing in script using AsOfMonth calendar.

Regards,

Ravi balar

apoorvasd
Contributor II

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

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

apoorvasd
Contributor II

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

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

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

I will try this and check.

Thank you.

mangalsk
Contributor III

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

You try it will work sure else share dummy file

Community Browser