topic Re: Set analysis help please in QlikView

Set analysis help please

Anonymous — Thu, 06 Feb 2014 13:57:45 GMT

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

Re: Set analysis help please

santharubban — Thu, 06 Feb 2014 16:24:53 GMT

can you post sample of your report

Re: Set analysis help please

agilos_mla — Thu, 06 Feb 2014 16:25:29 GMT

This should do the job

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

Michael

Re: Set analysis help please

Anonymous — Mon, 10 Feb 2014 10:37:40 GMT

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

Re: Set analysis help please

Mon, 10 Feb 2014 11:02:25 GMT

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.

Re: Set analysis help please

Anonymous — Mon, 10 Feb 2014 12:06:11 GMT

Checked its all linked correctly

Re: Set analysis help please

Anonymous — Mon, 10 Feb 2014 12:08:14 GMT

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

Re: Set analysis help please

rajeshvaswani77 — Mon, 10 Feb 2014 12:11:23 GMT

Hi Adil,

Please refer my article at

Romancing with Set Analysis

thanks,

Rajesh Vaswani

Re: Set analysis help please

agilos_mla — Mon, 10 Feb 2014 12:12:57 GMT

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

Re: Set analysis help please

Anonymous — Mon, 10 Feb 2014 12:16:35 GMT

This worked but only brings up current week selection.

But it works, before only got a 0

Thanks

Re: Set analysis help please

Anonymous — Tue, 11 Feb 2014 10:37:27 GMT

Hi All

I found something which could be an issue. There is in the week column

week

Jan Adj

Feb Adj

Do you think because of these words in the number field the syntax is not working?

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

Re: Set analysis help please

agilos_mla — Tue, 11 Feb 2014 10:45:26 GMT

Yes Adil,

As I said in my previous answer the "Adj" column in Week makes ther set analynis not perform correctly. Does it have a business meaning otherwise try to filter it in the load script

WHERE wildmatch(Week, '*Adj*') = 0;

Michael

Re: Set analysis help please

Tue, 11 Feb 2014 11:38:19 GMT

Hi try:

Sum({<Week={'$(=Max({<Week-={'*Adj*'}>}Week)+1)'}>} [Budget Vol])

sum({<Week={'$(=(max(Week)+1))>}[Budget Vol])

Re: Set analysis help please

mikecrengland — Tue, 11 Feb 2014 19:20:34 GMT

I think the problem is that set analysis is calculated once for the chart, not row by row. So if you are trying to display multiple weeks (for example a bar chart with week as a dimension) QlikView gets confused. I had a similar problem and solved it with a bunch of variables and a valuelist. I wrote it up at Fortune Cookie BI: If you can't learn it after six times...

It doesn't sound like you need your user to decide on the offset, but this might help.

Mike

Re: Set analysis help please

Fri, 14 Feb 2014 20:05:19 GMT

try like this hope will helps you

create variable as

Vmaxweek=max(Week)+1

sum({<Week={$(Vmaxweek)}Budget Vol])