topic Re: Weekly , Monthly , Yearly Compare Help in QlikView

Weekly , Monthly , Yearly Compare Help

amit_saini — Wed, 14 Sep 2016 10:13:34 GMT

Hi Folks,

I'm having below expression:

OEE=

if(sum([Act Planned Production Hours]) = 0, avg([Act OEE]),

sum([Act OEE]*[Act Planned Production Hours])/sum([Act Planned Production Hours]))

Now value of above exprssion I have to compared based on last week , last month , last Year

For example :

if (Week 38 value of OEE is > last week i.e . 37 ,'▲','▼')

similarly if (Sep value of OEE > Aug Value of OEE,'▲','▼')

and if (2016 OEE > 2015 OEE ,'▲','▼')

Result should be something like below:

Any Suggestions???

Thanks,

Re: Weekly , Monthly , Yearly Compare Help

gferran — Wed, 14 Sep 2016 10:22:52 GMT

Hi,

you can use a table wiht as many meassures as needed. In each meassure you have to use set analysis to fix the current week and the compared week.

Regards,

Gabriel

Re: Weekly , Monthly , Yearly Compare Help

shraddha_g — Wed, 14 Sep 2016 10:50:05 GMT

Hi Amit,

Try with below expression for

(2016 OEE > 2015 OEE ,'▲','▼')

if(

(if(sum({<Year = {"$(=max(Year))"}>} [Act Planned Production Hours]) = 0, avg({<Year = {"$(=max(Year))"}>} [Act OEE]),

sum({<Year = {"$(=max(Year))"}>} [Act OEE]*[Act Planned Production Hours])/sum({<Year = {"$(=max(Year))"}>} [Act Planned Production Hours])))

(if(sum({<Year = {"$(=max(Year)-1)"}>} [Act Planned Production Hours]) = 0, avg({<Year = {"$(=max(Year)-1)"}>} [Act OEE]),

sum({<Year = {"$(=max(Year)-1)"}>} [Act OEE]*[Act Planned Production Hours])/sum({<Year = {"$(=max(Year)-1)"}>} [Act Planned Production Hours])))

,1,0)

For Month and Week comparison, just change set analysis.

Regards,

Shraddha

Re: Weekly , Monthly , Yearly Compare Help

varshavig12 — Wed, 14 Sep 2016 11:38:35 GMT

I'm not very sure,

But you can try something like this:

Example: for Month--->. if (Sep value of OEE > Aug Value of OEE,'▲','▼')

1. You can make a straight table

2. Dimensions: YearMonth (eg: 201607) , Your_Field1

3. Expression: Create a Month over Month comparison ---> eg: sum((Sale_Amount)/Convert)

Growth --- diff of two month using above() --->

eg: =(sum((Sale_Amount)/Convert)-above(sum((Sale_Amount)/Convert)))/above(sum((Sale_Amount)/Convert))

4. Hide everything and just keep Growth expression.

5. Also, in presentation check horizontal.

6. Then you can format everything, like giving background colors and all.

I'm not sure.

Re: Weekly , Monthly , Yearly Compare Help

varshavig12 — Wed, 14 Sep 2016 11:39:28 GMT

I think , I'm missing something.

Re: Weekly , Monthly , Yearly Compare Help

varshavig12 — Wed, 14 Sep 2016 11:46:28 GMT

May be like this:

Re: Weekly , Monthly , Yearly Compare Help

sunny_talwar — Wed, 14 Sep 2016 12:42:00 GMT

May be use set analysis to get previous week, month and year values to do a comparison:

The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync

Re: Weekly , Monthly , Yearly Compare Help

amit_saini — Thu, 15 Sep 2016 07:23:18 GMT

Thanks for the suggestion , could u plz check why this is not working for Week comparison:

if(

(if(sum({<Week = {"$(=max(Week))"},Year_KPI=,POPER_NEW=,Week=>} [Act Planned Production Hours]) = 0, avg({<Week = {"$(=max(Week))"},Year_KPI=,POPER_NEW=,Week=>} [Act OEE]),

sum({<Week = {"$(=max(Week))"},Year_KPI=,POPER_NEW=,Week=>} [Act OEE]*[Act Planned Production Hours])/sum({<Week = {"$(=max(Week))"},Year_KPI=,POPER_NEW=,Week=>} [Act Planned Production Hours])))

(if(sum({<Week = {"$(=max(Week)-1)"},Year_KPI=,POPER_NEW=,Week=>} [Act Planned Production Hours]) = 0, avg({<Week = {"$(=max(Week)-1)"},Year_KPI=,POPER_NEW=,Week=>} [Act OEE]),

sum({<Week = {"$(=max(Week)-1)"},Year_KPI=,POPER_NEW=,Week=>} [Act OEE]*[Act Planned Production Hours])/sum({<Week = {"$(=max(Week)-1)"},Year_KPI=,POPER_NEW=,Week=>} [Act Planned Production Hours]))),

'▲' &num((if(sum([Act Planned Production Hours]) = 0, avg([Act OEE]),

sum([Act OEE]*[Act Planned Production Hours])/sum([Act Planned Production Hours]))

),'#.##0,0%')

,'▼' & num((if(sum([Act Planned Production Hours]) = 0, avg([Act OEE]),

sum([Act OEE]*[Act Planned Production Hours])/sum([Act Planned Production Hours]))

),'#.##0,0%'))

Thanks,

Re: Weekly , Monthly , Yearly Compare Help

shraddha_g — Thu, 15 Sep 2016 07:30:13 GMT

Hi amit,

Add year aslo in set analysis. If week comparision for current year then you have to mention it in set analysis.

Else it will compare week 2 value of all years with week 1 value of all years.

To avoid that you will have to specify which year to consider for week comparision.

You will have to do same for month comparision.

in your expression, you have nulified week also. you will have to remove that.

if(

(if(sum({<Week = {"$(=max(Week))"},Year_KPI=,POPER_NEW=,Week=>} [Act Planned Production Hours]) = 0, avg({<Week = {"$(=max(Week))"},Year_KPI=,POPER_NEW=,Week=>} [Act OEE]),

sum({<Week = {"$(=max(Week))"},Year_KPI=,POPER_NEW=,Week=>} [Act OEE]*[Act Planned Production Hours])/sum({<Week = {"$(=max(Week))"},Year_KPI=,POPER_NEW=,Week=>} [Act Planned Production Hours])))

(if(sum({<Week = {"$(=max(Week)-1)"},Year_KPI=,POPER_NEW=,Week=>} [Act Planned Production Hours]) = 0, avg({<Week = {"$(=max(Week)-1)"},Year_KPI=,POPER_NEW=,Week=>} [Act OEE]),

sum({<Week = {"$(=max(Week)-1)"},Year_KPI=,POPER_NEW=,Week=>} [Act OEE]*[Act Planned Production Hours])/sum({<Week = {"$(=max(Week)-1)"},Year_KPI=,POPER_NEW=,Week=>} [Act Planned Production Hours]))),

'▲' &num((if(sum([Act Planned Production Hours]) = 0, avg([Act OEE]),

sum([Act OEE]*[Act Planned Production Hours])/sum([Act Planned Production Hours]))

),'#.##0,0%')

,'▼' & num((if(sum([Act Planned Production Hours]) = 0, avg([Act OEE]),

sum([Act OEE]*[Act Planned Production Hours])/sum([Act Planned Production Hours]))

),'#.##0,0%'))

Regards,

Shraddha

Re: Weekly , Monthly , Yearly Compare Help

amit_saini — Thu, 15 Sep 2016 08:08:30 GMT

Even tried something below for Week comparison , but not working:

=if(

(if(sum({$<%DATE_Key={">=$(=WeekStart(today()))<=$(=WeekEnd(today()))"}>}[Act Planned Production Hours]) = 0, avg({$<%DATE_Key={">=$(=WeekStart(today()))<=$(=WeekEnd(today()))"}>}[Act OEE]),

sum({$<%DATE_Key={">=$(=WeekStart(today()))<=$(=WeekEnd(today()))"}>}[Act OEE]*[Act Planned Production Hours])/sum({$<%DATE_Key={">=$(=WeekStart(today()))<=$(=WeekEnd(today()))"}>}[Act Planned Production Hours])))

(if(sum({$<%DATE_Key={">=$(=WeekStart(today()-7))<=$(=WeekEnd(today()-7))"}>}[Act Planned Production Hours]) = 0, avg({$<%DATE_Key={">=$(=WeekStart(today()-7))<=$(=WeekEnd(today()-7))"}>}[Act OEE]),

sum({$<%DATE_Key={">=$(=WeekStart(today()-7))<=$(=WeekEnd(today()-7))"}>}[Act OEE]*[Act Planned Production Hours])/sum({$<%DATE_Key={">=$(=WeekStart(today()-7))<=$(=WeekEnd(today()-7))"}>}[Act Planned Production Hours])))

'▲' &num((if(sum([Act Planned Production Hours]) = 0, avg([Act OEE]),

sum([Act OEE]*[Act Planned Production Hours])/sum([Act Planned Production Hours]))

),'#.##0,0%')

,'▼' & num((if(sum([Act Planned Production Hours]) = 0, avg([Act OEE]),

sum([Act OEE]*[Act Planned Production Hours])/sum([Act Planned Production Hours]))

),'#.##0,0%'))

Thanks,
AS

Re: Weekly , Monthly , Yearly Compare Help

shraddha_g — Thu, 15 Sep 2016 09:41:34 GMT

check date format of WeekStart() and WeekEnd() is same as that of %DATE_Key