topic Re: set analysis for six months in QlikView

set analysis for six months

Tue, 20 Jan 2015 12:47:54 GMT

Hi,

I have the following 2 expressions:

sum({$<OrderYear = {$(=max(OrderYear))}>} AMOUNT) // summary of sales for the latest year

sum({$<OrderYear = {$(vBudgetYear)}>} Budget) //vBudgetYear is defined as one year before of the latest year (so I'd have a full year of budget)

I need to sum the budget (second line) only for the first 6 months (Jan to Jun - the amount of months I have in the current year)

How can I write the set analysis for this?

Thanks,

Assaf

Re: set analysis for six months

krishna20 — Tue, 20 Jan 2015 12:55:44 GMT

Hi Assaf,

How you are fixing budget?

I think monthly wise right? Is total budget of the year you calculating like this?

JAN + FEB+MAR+APR+MAY+JUNE+..................................DEC.

Regards

Krishna

Re: set analysis for six months

Tue, 20 Jan 2015 12:59:01 GMT

The budget is monthly, so I need a set analysis for OrderMonth. and not hard coded, of course.

Re: set analysis for six months

krishna20 — Tue, 20 Jan 2015 13:10:28 GMT

Hi,

Try this

=sum({$<Orderedyear= {'>=$(=Max(YearStart(today())))<=$(=max(monthend(today()))-6)'}>}Budget)

Regards

Krishna

Re: set analysis for six months

Anonymous — Tue, 20 Jan 2015 13:24:29 GMT

Hi,

Try:

=sum({$<Orderedyear= {'>=$(=num(addmonths(today(),-6)))'}>}Budget)

Regards

Neetha

Re: set analysis for six months

krishna20 — Tue, 20 Jan 2015 13:29:54 GMT

Hi Assaf,

Sorry a small mistake,neetha corrected it.But take it as 7 for june month

=sum({$<Orderedyear= {'>=$(=Max(YearStart(today())))<=$(=max(monthend(today(),-7)))'}>}Budget)

Regards

Krishna

Re: set analysis for six months

Tue, 20 Jan 2015 13:33:09 GMT

tried this:

='sum({$<OrderYear = {$(vBudgetYear)},OrderMonth = {'>={1} <= {6}'}>}Budget)

even this won't work.....

Re: set analysis for six months

tcullinane — Tue, 20 Jan 2015 13:35:27 GMT

sum({$<OrderYear = {$(vBudgetYear)}, OrderMonth = {'Jan','Feb','Mar','Apr','May','Jun'}>} Budget)

Re: set analysis for six months

Wed, 21 Jan 2015 08:09:05 GMT

I finally got it to work:

sum({$<OrderYear = {$(vBudgetYear)}, OrderMonth = { '>=$(v1stMonth) <=$(vlastMonth)'} >} Budget)

v1stMonth = num(month(date(39820))) // I needed January.....

vlastmonth =num(month(max(OrderDate)))

Thank you all!!