Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
gupta-anki
New Contributor III

Need Help in Converting Horizontal data as filter

Hi All,

I am dealing with a situation in Qlik Sense dashboard , where my Monthly Expenses data spans Horizontally in the excel (Excel file attached for reference) . Now I need to place a filters for Months, Qtr and Year and I am not sure how to do that .

When I am loading data each column is coming separately in Qlik which will not solve the purpose to create filter.

Can someone please help in this regard.

Thx
Ankit

10 Replies
mayuresh_d
Contributor

Re: Need Help in Converting Horizontal data as filter

hi ankit,

   please go to transformation step while loading excel into the qlikview.

when u click enable transformation step button one window will open just click on next then in next window u will find option crosstable. click on that, then crosstable window will open there will u find three attributes. qualifier field,attribute field and date field. in qualifier field insert 1, then in attribute field insert month which will be like field name for months, and last will be field name for data. then click ok and then finish.

and load data. this will solve ur problem.

Thanks & regards,

Mayuresh

mayuresh_d
Contributor

Re: Need Help in Converting Horizontal data as filter

hi aniket,

one more take resident of this table by that u will do any operation u that u want to do on that table.

following is the script which will appear

where F1 is my projevt field and Jan is my month field and data is my data field:

table1:

CrossTable(Jan, Data)

LOAD F1,

     Jan,

     Feb,

     Mar,

     Apr,

     May,

     Jun,

     Jul,

     Aug,

     Sep,

     Oct,

     Nov,

     Dec

FROM

D:\Calendar_Filter.xlsx

(ooxml, embedded labels, table is Sheet1);

table :

load

Jan

,Data,

F1,

1 as flag

resident table1;

drop table table1;

thanks and regards,

Mayuresh

gupta-anki
New Contributor III

Re: Need Help in Converting Horizontal data as filter

Thanks a ton Mayuresh:

This is the script of my Data:

[Forecast]:

LOAD [Department/Program Name]as [Program Name],

  [Project/Initative Name]as [Project Name],

  [Project Alias],

  //[Type],

  [Spend Type],

  //[Project#],

  //[CC],

  [Vendor Name/Description] as [Vendor Name],

  //[Account],

  [% Allocation],

  [Resource Name or PO #]as [PO Number],

  [Resource Commit or PO Amount] as [PO Amount],

  //[Rate $/hr],

  //[Est Monthly Burn],

  //[Comments],

  [Jan],

  [Feb],

  [Mar],

  [Apr],

  [May],

  [Jun],

  [Jul],

  [Aug],

  [Sep],

  [Oct],

  [Nov],

  [Dec],

  [Year Total] as [Forecast Spend],

  [Jan1] as [Jan Spend],

  [Feb1] as [Feb Spend],

  [Mar1] as [Mar Spend],

  [Apr1] as [Apr Spend],

  [May1] as [May Spend],

  [Jun1] as [June Spend],

  [Jul1] as [Jul Spend],

  [Aug1] as [Aug Spend],

  [Sep1] as [Sep Spend],

  [Oct1] as [Oct Spend],

  [Nov1] as [Nov Spend],

  [Dec1] as [Dec Spend],

  [Year Total1] as [Actual Spend]

FROM [lib://Apps/QLIk Sense Sample File.xlsx]

(ooxml, embedded labels, header is 4 lines, table is Forecast);

Can you please suggest what changes should I make so that I can get :

1 filter for Month which will show Data like Jan , Feb , Mar

2 Filter for Qtr, which Will Show Q1, Q2.. Q4 (Where Q1 = Jan , Feb Mar)

Thx

Ankit

gupta-anki
New Contributor III

Re: Need Help in Converting Horizontal data as filter

Hi Mayuresh, one clarification, I am preparing Dashboard in Qlik Sense not Qlik View

mayuresh_d
Contributor

Re: Need Help in Converting Horizontal data as filter

if possible please send me your excel file please

gupta-anki
New Contributor III

Re: Need Help in Converting Horizontal data as filter

Hi I have attached the excel "File Named" as Sample

mayuresh_d
Contributor

Re: Need Help in Converting Horizontal data as filter

hi,

you need to load table two times it can be achieved using transformation steps.

1st step:

go to transformation window and in delete option delete First three rows and convert all other table using cross table where in qualifier fields attribute ,select columns till just before the months column. 

2nd step:

again load same excel then enable transformation then delete all rows except 1st three, then rotate that rows to left and load data now u have twp table in which u have month field common.

this will solve ur problem.

thanks and regards

Mayuresh

gupta-anki
New Contributor III

Re: Need Help in Converting Horizontal data as filter

Hi but I am making Dashboard in Qlik Sesne, is there any transformation window in that?

Thx

Ankit

mayuresh_d
Contributor

Re: Need Help in Converting Horizontal data as filter

hi,

there is no crosstable wizard in qliksense u have to do it by using qlikview and then copy that script else

"crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )"

use this syntax and

QlikSense does have a transformation wizard but its not as robust as QlikView yet. You always use the transformation wizard in QV and paste the code in QlikSense.


thanks and regards

Mayuresh