Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
have you tried
sum(Inventory*MaxWeek_Flag)
Or
sum(if(SnpshtYYYYWK=aggr(max(SnpshtYYYYWK),SnpshtYYYYQ),Inventory,0))
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.
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.
Will you be able to share a sample or sample data to look at what you have?
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
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))
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;
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.
Thanks Sunny.