Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hiya
I need help with Set- Analysis please.
What i wanted to do was to set up my report so if the user selects week 1, budget volume goes to week 2
(used for next week results)
My field in script for week is
Week_No
And my budget volume is
[Budget Vol]
Thanks
Can someone advise good place to learn set analysis been on here but don't know how to use it
can you post sample of your report
This should do the job
Sum({<Week_No={$(=Max(Week_No)+1)}, Week_No=>} [Budget Vol])
Michael
Hi Michael
I tried what you said,
Sum({<Week_No={$(=Max(Week_No)+1)}, Week_No=>} [Budget Vol])
This looks 100% right but didn't work.
Could i be doing something else wrong if this doesn't work? Thanks for your help
The expresion as suggested by Michael looks good. Can you please test your data by creating a table box, with Week_No and [Budget Vol] as fields. I think may be Budget Vol is not correctly mapped with the Week_No.
Can you post a sample app.
Checked its all linked correctly
All data is in one excel sheet which contains all data
as below
Data:
LOAD Country,
Week,
Month,
Year,
Volume,
Income,
[Variable Costs],
[Gross Margin],
Overheads,
[Op Profit],
[MTD OP Profit],
[YTD OP Profit],
[MTD Vol],
[YTD Vol],
EBITDA,
[MTD EBITDA],
[YTD EBITDA],
[Budget Vol],
[Budget Vol MTD],
[Budget Vol YTD],
[Budget OP],
[Budget OP MTD],
[Budget OP YTD],
[Budget EBITDA],
[Budget EBITDA MTD],
[Budget EBITDA YTD],
[PY Vol],
[PY VOL MTD],
[PY VOL YTD],
[PY Op Profit],
[PY Op Profit MTD],
[PY Op Profit YTD],
[PY EBITDA],
[PY MTD EBITDA],
[PY YTD EBITDA],
[Vol Week +1],
[Vol Week +2],
[Vol Week +3],
[Vol Week +4],
[Vol Week +5],
[Profit Week + 1],
[Profit Week + 2],
[Profit Week + 3],
[Profit Week + 4],
[Profit Week + 5],
[EBITDA Week + 1],
[EBITDA Week + 2],
[EBITDA Week + 3],
[EBITDA Week + 4],
[EBITDA Week + 5],
[RFC Month Vol],
[RFC Month Profit],
[RFC Month EBITDA],
[Vol LW FC],
[OP LW FC],
[EBITDA LW FC]
FROM
[Consolidated P&L\ALL Data Sheet.xlsx]
(ooxml, embedded labels, table is Data);
Adil,
Try this expression, I disregard *Adj value otherwise indeed the Max(Week) cannot be performed correctly :
Sum({<Week={$(=Max({<Week-={'*Adj*'}>}Week)+1)}, Week=>} [Budget Vol])
Otherwise you should try to find a way to standardize the Week_No as a number for all values using DUAL, for instance.
Michael
This worked but only brings up current week selection.
But it works, before only got a 0
Thanks