Need Help in Converting Horizontal data as filter

[Anonymous]

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

[mayuresh_d]

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]

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

[Anonymous]

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

[Anonymous]

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

[mayuresh_d]

if possible please send me your excel file please

[Anonymous]

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

[mayuresh_d]

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

[Anonymous]

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

Thx

Ankit

[mayuresh_d]

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