topic Re: Budgeting problems in QlikView

Budgeting problems

Sat, 04 May 2013 07:15:38 GMT

hi,

I am a newbie to qlikview and not an IT-expert at all, and I got the following problem:

In our Oracle database we have budget figures of our customers on a yearly base,

example:

customer_ID

Article_ID

Budget

These budget values are being add on yearly basis,
how to show monthly or quarterly values (subject to selection criteria) ?

For time being I am only shown the yearly value, no matter which selection I make

-this is not helpful of course because I cannot trace the deviation of sales vs budget

Many thanks for your help/hints !

Martin

Re: Budgeting problems

Gysbert_Wassenaar — Sat, 04 May 2013 07:51:46 GMT

Divide the budget by 4 if you're using quarters in your chart. Divide it by 12 if your using months. You could create a variable for this that calculates the divider depending on the selection.

Re: Budgeting problems

vikram_patro — Sat, 04 May 2013 08:28:13 GMT

Hi Martin

Attached is an example to transform data evenly based on the record count.

Hope this helps!

Regards,

Vikram

Re: Budgeting problems

Sat, 04 May 2013 11:10:32 GMT

Hi,

I'll check this on monday when back in the office, thx for the moment and nice weekend to all of you!

Martin

Re: Budgeting problems

Mon, 06 May 2013 06:07:54 GMT

Hi Vikram,

just checked this, so far it looks quite good and responds to what I was looking for.

Concerning the forumula behind, I do not get the result, to be honest.

Example:

If I choose Q1/2011 over all articles and customer IDs,

a) the total Budget should read 900t (3600/4)

b) customer ID 300-Article 1- should read 225 instead 221,917

customer ID 100-Article 1- should read 325 instead 320,547

customer ID 200-Article 2- should read 350 instead 345,205

or am I mistaken?

Thanks for your info on this

Regards,

Martin

PS: below our Script (my colleague from IT is good in data banks but not an expert in qlikview, too)

How should this be changed to achieve same results as you did in your script?

________________________________

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';

SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

ODBC CONNECT TO [MASPROD;DBQ=MASPROD] (XUserId is XXX, XPassword is XXX);

Directory;

LOAD

year(DATUM)&SORTE&KD_NR as key,

year(DATUM) as Jahr,

month(DATUM) as Monat,

day(DATUM) as Tag,

AUFTRAG_NR & '/' & AUFTRAG_POSITION_NR as Auftrag,

FIRMENGRUPPE_NR & ' ' & FG_NAME as FG,

if(KUNDE_ART='K',KD_NR,ECHTE_KD_NR) & ' ' as Kunde,

LAND_BZ_KURZ as Region,

where DATUM>= '01.01.2008' and MANDANT_NR=1;

SQL SELECT *

FROM MASABFWEI.c171_v_faktura ;

LOAD

JAHR&SORTE&SCHLUESSEL as key,

JAHR as Jahr_B,

SCHLUESSEL,

SORTE as Sorte_B

,PLANMENGE_TO,PLANPREIS_100KG

where JAHR>2008;

SQL select * from MASABFWEI.C171_ABSATZPLAN_PLAN_DATEN;

LOAD 'Budget' as Monat,

JAHR as Jahr,

3 as KM_NR,

select *

from C171_LANDGRUPPE_BUDGET;

Re: Budgeting problems

Mon, 06 May 2013 06:33:53 GMT

Hi Gysbert,

I cannot follow your advise, to be honest (I am a Newbie and I'd need step by step hints)
or sample qlik files to follow/understand.

Regards,

Martin

Re: Budgeting problems

vikram_patro — Wed, 08 May 2013 13:29:00 GMT

You are right regarding the example you have quoted above. The data is distributed across quarters on basis of day (Ex: for year 2011 - Q1 = 90 days, Q2 = 91, Q3 = 92, Q4 = 92). So the data is not distributed as 3600/4.

Let me know if you need it in that way.

Also, if you can provide me the data in Excel file, i will populate the actual budget data.

Thanks,

Vikram

Re: Budgeting problems

Wed, 08 May 2013 13:38:46 GMT

Hello Vikram,

I have solved this budgeting issue meanwhile but we have discovered another fault

in our database, unfortunately.

In our oracle base we have sometimes customers not being budgeted in the run of a year,

but we have generated sales figures for them. Now, due to the fact they are not budgeted (also not with "0")

the sales figures do not appear which is a fault.

Think we have to call an Expert to generate the right file.

But many thanks for your advise!

Regards,

Martin