Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

show colums as dimension

hello

i have table like this :

employee_keyjanfebmaraprmayjunjulaugsepoctnovdecyear_salary
110000111111140000
211111100011150000
301100011111020000
411111111100030000

i want to create chart

that show the data like this:

monthcount of employees
jan3
feb3
mar3
apr2
may1
jun3
jul3
aug2
sep4
oct3
nov3
dec2

any suggestions??

nati

8 Replies
sunilkumarqv
Specialist II
Specialist II

Try like this

MK_QSL
MVP
MVP

CHECK enclosed file..

PrashantSangle

Hi,

See enclosed file,

It is as per your requirement.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
MarcoWedel

Hi Nati,

the required script for this task can be created with the crosstable wizard:

In the script editor import your example data like this

QlikCommunity_Thread_110705_Pic1.JPG.jpg

QlikCommunity_Thread_110705_Pic2.JPG.jpg

QlikCommunity_Thread_110705_Pic3.JPG.jpg

select the crosstable wizard here

QlikCommunity_Thread_110705_Pic4.JPG.jpg

QlikCommunity_Thread_110705_Pic5.JPG.jpg

enter the field names and finish the import wizard.

you should get this script:

tabCrossTable:

CrossTable(Month, Employee)

LOAD employee_key,

     jan,

     feb,

     mar,

     apr,

     may,

     jun,

     jul,

     aug,

     sep,

     oct,

     nov,

     dec

FROM

[http://community.qlik.com/thread/110705]

(html, codepage is 1252, embedded labels, table is @1);

Afterwards you simply generate a table chart with the dimension "month"  and expression "=sum(Employee)" named "count of employees".

The generated table should look like this:

QlikCommunity_Thread_110705_Pic7.JPG.jpg

To correct the sorting of the month column (and the data type of the month field), you could add this code:

Left Join (tabCrossTable)

LOAD Distinct

  Month,

  Date#(Month, 'MMM') as Month2

Resident tabCrossTable;

DROP Field Month;

RENAME Field Month2 to Month;

afterwards your table should have changed into:

QlikCommunity_Thread_110705_Pic8.JPG.jpg

hope this helps

regards

Marco

Not applicable
Author

thanke you all , you are very helping, i learned a lot about the crosstable option.

any way i wonder if there is a solution in the report level instead in script level

i have a big model with tables including milions of records and i am not sure that i want to duplicate large table

just for this report.

for this small report i prefer a solution in the report level and i dont care if it will takes a little longer to show him.

i have large 7 fact table in my complicated model and looking for creative way to solve this.

any suggestions?

thank you all

nati

MarcoWedel

Hi Nati,

there is another option indeed without changing the load script and data model.

Create a table chart with this calculated dimension:

=valuelist('jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov', 'dec')

and this expression:

=pick(match(valuelist('jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov', 'dec'), 'jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov', 'dec'),

sum(jan),

sum(feb),

sum(mar),

sum(apr),

sum(may),

sum(jun),

sum(jul),

sum(aug),

sum(sep),

sum(oct),

sum(nov),

sum(dec)

)

result looks the same

regards

Marco

Not applicable
Author

thanke you marco, you are very helping,

i will try this

nati

MarcoWedel

You're welcome.

Please go ahead and close this thread, when there are no further questions.

thanks

regards

Marco