Budgeting problems

[#1]

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

[Gysbert_Wassenaar]

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.

[vikram_patro · Accepted solution]

Hi Martin

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

Hope this helps!

Regards,

Vikram

[#4]

Hi,

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

Martin

[#5]

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;

*/

[#6]

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

[vikram_patro]

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

[#8]

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