8 Replies Latest reply: Jan 26, 2017 11:55 AM by Carlos Londono

# Max week in quarter-Current week

Hello community,

I have the following challenge. The inventory should be the amount in the max week in the quarter. If it is in the current quarter, the inventory amount should be the current week's inventory amount.

My inventory table uses weekly snapshots.  The quarter inventory amount should be the amount of the snapshot of the quarter’s last week.  I am using a max week in quarter flag (MaxWeek_Flag = 1 if week is max week in quarter).

Using this formula:

Sum({<MaxWeek_Flag={1}>}Inventory)

It gives me the values except for the current quarter (20171)

This other formula gives me correct values except for the last quarter, it aggregates all the weeks in the last quarter instead of returning the current week as the max week in the quarter:

if(Sum({<MaxWeek_Flag={1}>}Inventory)=0,

Sum({<SnpshtYYYYWK={"<=\$(cCurrent_Week_YYYYWK)"}>}Inventory),

Sum({<MaxWeek_Flag={1}>}Inventory))

Any ideas how to achieve this?

Carlos

• ###### Re: Max week in quarter-Current week

have you tried

sum(Inventory*MaxWeek_Flag)

Or

sum(if(SnpshtYYYYWK=aggr(max(SnpshtYYYYWK),SnpshtYYYYQ),Inventory,0))

• ###### Re: Max week in quarter-Current week

Hi,

You'll need to insert the script attached in your Data Model or/source.

Master Calendar Script

if(InWeek(TempDate,today(),-1),1,0) as CQW,

 if(CQW=1 and len(QuarterEnd([Effective Date]))>0,1,0) as CQMW,

And in front end use CQMW as flag

Sum({<CQMW={1}>}Inventory)

CQMW will give you Max week for current quarter (01-09-2017 to 01-15-2017) as this week has not yet finished so it will show previous week as Max.

• ###### Re: Max week in quarter-Current week

Will you be able to share a sample or sample data to look at what you have?

• ###### Re: Max week in quarter-Current week

Hi Sunny,

Attached is a  QVW with sample data.  I realized I posted thread to QlikSense and actually it is a QlikView question.  Let me know if I should close it and open in QlikView.

Thanks,

Carlos

• ###### Re: Max week in quarter-Current week

1 option would be to use this expression:

2nd option would be to to add a MaxWeek Flag to 201702 in the script may be using a logic like this

Table:

YYYYQ,

YYYYWK

FROM....

Left Join (Table)

YYYYQ,

Max(YYYYWK) as YYYYWK,

1 as MaxWeek_Flag

Resident Table

Once you do this, you should be able to use just this expression without any if statement

Sum({<MaxWeek_Flag={1}>}Inventory)

3rd option would be to use this

if(Sum({<MaxWeek_Flag={1}>}Inventory)=0,

Sum({<YYYYWK={"\$(=Max(YYYYWK))"}>}Inventory),

Sum({<MaxWeek_Flag={1}>}Inventory))

• ###### Re: Max week in quarter-Current week

Thanks for your help Sunny,  If I am using a cyclic group with dimensions such as Region, Country, Division, Business, Product, etc. should I modifiy the expression to aggregate at the highest level or how should I address that issue?  For example:

FirstSortedValue(Aggr(Sum(Inventory),Region, YYYYQ, YYYYWK), -Aggr(YYYYWK, Region, YYYYQ, YYYYWK))

Your recommended solution works when I have a single dimension, but it breaks if I use a cyclic group or a table with multiple dimensions. If I use your recommended 2nd option, should I aggr at the highest level like this?

Table:

YYYYQ,

YYYYWK

FROM....

Left Join (Table)

YYYYQ,

Max(YYYYWK) as YYYYWK,

1 as MaxWeek_Flag

Resident Table

Group By Region, YYYYQ;

• ###### Re: Max week in quarter-Current week

You can try like this for your cycle group.... assuming your cycle group name is Cycle_Group

FirstSortedValue(Aggr(Sum(Inventory), \$(='[' & GetCurrentField([Cycle_Group]) & ']'), YYYYQ, YYYYWK), -Aggr(YYYYWK, \$(='[' & GetCurrentField([Cycle_Group]) & ']'), YYYYQ, YYYYWK))

\$(='[' & GetCurrentField([Cycle_Group]) & ']') should give you the dimension name for your cycle group within the Cycle_Group.

• ###### Re: Max week in quarter-Current week

Thanks Sunny.