Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Trouble to show some months ago

Hi, to all,

Until yesterday this option was working fine, but when I changed my Cross Table to concatenate two field (T$PERI$O) this mean Month, then it not working anymore (see attached file that explain and show the setup option, I´ll appreciate your help to find the mistake.

Tanks in advance,

4 Replies
Miguel_Angel_Baeyens

Hi,

First question is: are both tables loading months from same year? The one you are showing is using MakeDate() with year 2011. Has the concatenated table values from months from year 2011 as well?

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Not applicable
Author

Hi Miguel, Thanks for, you quick response

Yes, Both Tables loading months from same year, the firts one is a Excell (Budget )and second one is my DB Oracle (Sales).

Yes, I need to concateate it because the Month is a Synckey.

I would like to compare Budget vs. real Sales from current year until tow months ago.

Miguel_Angel_Baeyens

Hi,

Are you marking in the concatenation the source of the records (Budget and Sales)? If you don't, and if the amount field name is the same for both budget and sales, you cannot distiguish between both.

So the concatenation should look like this

Sales:

LOAD Date,

     Amount,

     'Sales' AS Source

FROM SalesSource;

Budget:

LOAD Date,

     Amount,

     'Budget' AS Source

FROM BudgetSource;

Then your expression to get the budget can be

Sum({< Source = {'Budget'} >} Amount)

And for sales

Sum({< Source = {'Sales'} >} Amount)

Anyway, the complete script and some sample data would help, I'm afraid I cannot understand the document you attached.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Not applicable
Author

Scrip

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

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

SET TimeFormat='hh:mm:ss TT';

SET DateFormat='DD/MM/YYYY';

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

SET MonthNames='Ene;Feb;Mar;Abr;May;Jun;Jul;Ago;Sep;Oct;Nov;Dic';

SET DayNames='Lun;Mar;Mié;Jue;Vie;Sáb;Dom';

OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=baan;Data Source=baan_10.72.102.100;Extended Properties=""] (XPassword is EbOWBRJOPDdQWSJOTTYCWQZNVF);

SQL SELECT *               (HERE is MY LES INFORMATION)

FROM BAAN.TTDSLS824804;  (Where T$PERI$O mean MONTH & T$DQUA$O Mean Sales Amount)

SQL    SELECT "T$COPR",   

"T$ITEM" "T$ITEM$O",

"T$CWAR",

"T$MATC",

"T$OPRC"

FROM BAAN.TTIITM001804;

SQL SELECT "T$ITEM" "T$ITEM$O",

"T$CWAR",

"T$STOC",

"T$ORDR",

"T$ALLO"

FROM BAAN.TTDINV001804;

SQL SELECT "T$ITEM" "T$ITEM$O",

"T$ORNO" "T$ORNO$O",

"T$PONO" "T$PONO$O",

"T$CUNO" "T$CUNO$O",

"T$OQUA",

"T$BQUA"

FROM BAAN.TTDSLS041804;

LOAD * INLINE [

T$PERI$O, Name

   1, Ene

   2, Feb

   3, Mar

   4, Abr

   5, May

   6, Jun

   7, Jul

   8, Ago

   9, Sep

  10. Oct

  11, Nov

  12, Dic

];

Step1:

crosstable(T$PERI$O, Cantidad, 4)  (T$PERI$O mean MONTH)

LOAD

T$YEAR$O,

T$CITG$O,

[Desc Familia],

T$ITEM$O,

[1] AS 1,

[2] As 2,

[3] AS 3,

[4] AS 4,

[5] As 5,

[6] AS 6,

[7] AS 7,

[8] AS 8,

[9] AS 9,

[10] AS 10,

[11] AS 11,

[12] AS 12

FROM

(biff, embedded labels, table is [data unidades y Dolares$]);

Step2:

NOCONCATENATE LOAD T$YEAR$O, T$CITG$O,[Desc Familia],T$ITEM$O,

Month(MakeDate(2011,Num(Num#(T$PERI$O)))) AS T$PERI$O,

Cantidad                          (Cantidad mean Budget AMOUNT)

DROP TABLE Step1; 

BUDGET EXCEL FILE Example

T$YEAR$OT$CITG$ODesc FamiliaT$ITEM$O123456789101112
2011PT7020Air Passenger(Air,Cabin,Panel)I-46340301511151215113
2011PT7020Air Passenger(Air,Cabin,Panel)I-47040196496221396131996131316
2011PT7020Air Passenger(Air,Cabin,Panel)I-421592235075205270
2011PT7020Air Passenger(Air,Cabin,Panel)I-42524030143914038140912
2011PT7020Air Passenger(Air,Cabin,Panel)I-46411114124224221
2011PT7020Air Passenger(Air,Cabin,Panel)I-423303014811611610