Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set analysis help please

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

Set Analysis Wizard for QlikView | qlikblog.at

14 Replies
santharubban
Creator III
Creator III

can you post sample of your report

agilos_mla
Partner - Creator III
Partner - Creator III

This should do the job

Sum({<Week_No={$(=Max(Week_No)+1)}, Week_No=>} [Budget Vol])

Michael

Anonymous
Not applicable
Author

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

Not applicable
Author

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.

Anonymous
Not applicable
Author

Checked its all linked correctly

Anonymous
Not applicable
Author

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

rajeshvaswani77
Specialist III
Specialist III

Hi Adil,

Please refer my article at

Romancing with Set Analysis

thanks,

Rajesh Vaswani

agilos_mla
Partner - Creator III
Partner - Creator III

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

Anonymous
Not applicable
Author

This worked but only brings up current week selection.

But it works, before only got a 0

Thanks