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: 
Anonymous
Not applicable

Help on Cross Table

Hi everyone, I've been doing this task for a week but I still can't get the outcome. I am doing a QlikView which would show the Total Average Cost per Area.

Here is my load script:

T1:

LOAD [Account Code],

     [Account Name],

     [Site Code],

     [Site Name],

     [Site Year],

     [Gross square feet (GSF)]

     //[JLL Service delivery cost - payroll, taxes, fringe (administration) - R&M],

     //[Electrical  - R&M],

     //[Elevator - R&M],

     //[Fire/life safety - R&M],

     //[General building exterior   - R&M],

     //[General building interior - R&M],

     //[HVAC  - R&M],

     //[Structural/roofing  - R&M],

     //[Plumbing - R&M],

     //[Pest control within the building - R&M],

     //[Miscellaneous / other - R&M]

FROM

(ooxml, embedded labels, table is Data);

Join

T2:

Load

[Site Name]&[Site Year] as key

Resident T1;

T3:

CrossTable(CostItem, Data, 1)

LOAD //[Account Code],

     //[Account Name],

     //[Site Code],

     //[Site Name],

     //[Site Year],

     [Site Name]&[Site Year] as  key,

     //[Gross square feet (GSF)],

     [JLL Service delivery cost - payroll, taxes, fringe (administration) - R&M],

     [Electrical  - R&M],

     [Elevator - R&M],

     [Fire/life safety - R&M],

     [General building exterior   - R&M],

     [General building interior - R&M],

     [HVAC  - R&M],

     [Structural/roofing  - R&M],

     [Plumbing - R&M],

     [Pest control within the building - R&M],

     [Miscellaneous / other - R&M]

FROM

(ooxml, embedded labels, table is Data);

And my on my Expression Tab, script is:

Sum(Aggr(Avg(Data/[Gross square feet (GSF)]),[Account Name]))

I have to exclude zero and negative values upon load but when I do it with:

e.g.

     Load   [Gross square feet (GSF)],

               If(  [Gross square feet (GSF)]>0,  [Gross square feet (GSF)])

     Resident T1;

     Load Data,

               If(Data>0,Data)

     Resident T3;

Nothing happens and when I run it takes a lot time for it to load.

Dimension is the Cost Item and Account Name? Actually it's just supposed to be CostItem but when I don't include Account Name as Dimension, the chart wouldn't display a grouped chart but instead it displays a stacked chart.

Where did I possibly go wrong? Please help and sorry in advance if I missed something so obvious as I am a beginner in QV.

Thanks in advance!

2 Replies
YoussefBelloum
Champion
Champion

Hi,

It is difficult to say what is wrong here without having seen the data structure and the expected output..

would you be able to share sample data and what you are expecting as an output ?

sunny_talwar

May be exclude nulls and 0s like this

T1:

LOAD [Account Code],

    [Account Name],

    [Site Code],

    [Site Name],

    [Site Year],

    [Gross square feet (GSF)]

    //[JLL Service delivery cost - payroll, taxes, fringe (administration) - R&M],

    //[Electrical  - R&M],

    //[Elevator - R&M],

    //[Fire/life safety - R&M],

    //[General building exterior  - R&M],

    //[General building interior - R&M],

    //[HVAC  - R&M],

    //[Structural/roofing  - R&M],

    //[Plumbing - R&M],

    //[Pest control within the building - R&M],

    //[Miscellaneous / other - R&M]

FROM

(ooxml, embedded labels, table is Data)

Where [Gross square feet (GSF)] > 0 and Len(Trim([Gross square feet (GSF)])) > 0;