Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

urgent help - is this possible ?? how to achive this in pivot table..??

Hi All,

am trying to get the totals at the end of each row in a pivot table.

Please find the attached sample data and am looking for the below senario using pivot table..

in the below table code, location and Division are dims and total occupency and available vacancy are the expressions.

am trying to get the below type of chart. but in my chart each expression is divided by each dimension and am not able to get the below type of charts.

Please guide me on how to build this type of chart. Thanks Much.

   

Division
CodeLocationMobileMediaWifiTVrouterCallerstotal occupencyavailable vacancy
A100London464142438786345380
A101EU544334191718185400
A102Japan624536222423212420
A103USA704738253128239440
A104Africa784940283833266460
A105Asia865142314538293480
A106India945344345243320500
A107China1025546375948347520
A108Iron1105748406653374540
A109France1185950437358401560
A110Germany1266152468063428580
7 Replies
Highlighted
Partner
Partner

Hi Vishal,

This seems to be the case to use Crosstable() statement.

x:

LOAD Code,

    Location,

    Mobile,

    Media,

    Wifi,

    TV,

    router,

    Callers

FROM

(ooxml, embedded labels, table is Plan1);

Cross:

CrossTable(Type,Value)

Load

Location,

    Mobile,

    Media,

    Wifi,

    TV,

    router,

    Callers

Resident x;

NewData:

Load

Distinct

Code,

Location

Resident x;

left join (NewData)

Load

*

Resident Cross;

drop tables Cross,x;

Attached the sample file i used (just copied your data into a spreadsheet.

With that, I got the following with a simple sum(Value) expression:

sample.png

Highlighted
Creator III
Creator III

Thanks Felip for your valuable time on this.

Hope my ask may not be clear to you, sorry for that.

i have 3 dimenstions and one expression out of these 2 dimension are normal and one should be pivoted. so the result should look like the above sample pivot table i have given in my post. there division is the third dimension and total occupency is my expression. is it possible to get the total column for the expression like i shown in above post.

if we sum up all the values in the first row we have to get 345. and value should come in a separate column as total which is not available in pivot table.

Any possibility to achive this with 3 dimensions, just got to know that with 2 dims its possible. any extension will help us to achieve this..??

Thanks Much Again !!

Highlighted
Partner
Partner

Sure,

Use the partial sums on the pivoted dimension and rename the column like this:

sample.png

Highlighted
Creator III
Creator III

Thanks again, till this it was fine, but when we add another expression it was calcuting that expression to each and every pivoted dimention which i dont want.

i want to calculate the expression : available Space.

and the above expression should come with an another column beside total occupency, so that i can calculate the % of occupency.

seems like for my second expression dim 2 and dim 3 needs to be disabled in the calculation..or some thing around this...

can we disable some dims while calculating the expression...??? is this possible... ???

please let me know if my above requirement is not clear...

Highlighted
Partner
Partner

Attached a sample QVW, with the following result:

sample.png

More then that, you're probably better with a straight table anyway.

Highlighted
Creator III
Creator III

Thanks Much Felip,

we are near to the solution, can you please check my origincal post where i have given my exact requirement.

the second expression that we added must be calculated only once, and  that to at the end beside the total occupency

am not sure on its possibility.

in your above solution second expression is calculated for all the 3rd dimension values.  Please check my main post.

Thanks again for your time...

Highlighted
Partner
Partner

That's the behaviour of the pivot table, you won't be able to calculate the expression only once for all the dimensions.

I'll have to use a straght table for that.