Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

adil_hussain
Contributor II

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
Contributor II

Re: Set analysis help please

can you post sample of your report

agilos_mla
Contributor III

Re: Set analysis help please

This should do the job

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

Michael

adil_hussain
Contributor II

Re: Set analysis help please

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

Re: Set analysis help please

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.

adil_hussain
Contributor II

Re: Set analysis help please

Checked its all linked correctly

adil_hussain
Contributor II

Re: Set analysis help please

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
Valued Contributor III

Re: Set analysis help please

Hi Adil,

Please refer my article at

Romancing with Set Analysis

thanks,

Rajesh Vaswani

agilos_mla
Contributor III

Re: Set analysis help please

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

adil_hussain
Contributor II

Re: Set analysis help please

This worked but only brings up current week selection.

But it works, before only got a 0

Thanks

Community Browser