Max week in quarter-Current week

[clondono]

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?

Thanks for your help

Carlos

[Anonymous]

have you tried

sum(Inventory*MaxWeek_Flag)

Or

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

[MK9885]

Hi,

I've posted Master Calendar Script in community, just follow this link.

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

Master Calendar Script

before reloading Master Calendar add 2 extra syntax to the script as follows.

In normal load paste this

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

and In preceding load I.E before the Load Statement paste

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.

[sunny_talwar]

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

[clondono]

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

[sunny_talwar]

1 option would be to use this expression:

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

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

Table:

LOAD Business,

          YYYYQ,

          YYYYWK

FROM....

Left Join (Table)

LOAD Business,

          YYYYQ,

          Max(YYYYWK) as YYYYWK,

          1 as MaxWeek_Flag

Resident Table

Group By Business, YYYYQ;

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

[clondono]

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:

LOAD Region,

          YYYYQ,

          YYYYWK

FROM....

Left Join (Table)

LOAD Region,

          YYYYQ,

          Max(YYYYWK) as YYYYWK,

          1 as MaxWeek_Flag

Resident Table

Group By Region, YYYYQ;

[sunny_talwar · Accepted solution]

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.

[clondono]

Thanks Sunny.