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: 
Naresh1
Contributor III
Contributor III

Month and Quarter goals

Hi everyone,

i have excel table, in that table having fields like jan20,feb20,mar20,Q1-20,Apr-20,May20,jun20,Q2-20,Jul20,Aug20,Sep20,  Q3-20,Oct20,Nov20,Dec20,Q4-20

in every field having percentages, 

in Qliksense , i want to show all these fields into two fields only, those field names "Month_goals" and  "Quarter_Goals"

In month_Goals  i want to show default  Mar20 percentages and in "Quarter_Goals" , i want to show default 'Q1-20' percentages

in filter pane i will take month and quater,

if i click on Q2-20 from Quarter field (Filterplane) then Q2_20 percentages will display in  "Quarter_Goals"

if i click on Feb20  from month (Filterplane) then feb20 percentages will display in "Month_goals".

TIA

Regards,

Naresh.

3 Replies
lblumenfeld
Partner Ambassador
Partner Ambassador

Here is the script...

First, load the data source into one table. I'll call mine "MyTable".

Then use this...

MonthGoals:
CrossTable (Month_name, Month_goals)
Load
   MyKeyField,   // This is the field that tells you what it's for, ex., EmployeeID, Department 
    Jan20,
    Feb20,
    Mar20,
    Apr-20,
    May20,
    Jun20,
    Jul20,
    Aug20,
    Sep20,
    Oct20,
    Nov20,
    Dec20
Resident MyTable; // Or whatever your data source is

QuarterGoals:
CrossTable (Quarter_name, Quarter_goals)
Load
   MyKeyField,   // This is the field that tells you what it's for, ex., EmployeeID, Department 
    Q1-20,
    Q2-20,
    Q3-20,
    Q4-20
Resident MyTable; // Or whatever your data source is

Drop Table MyTable;

 

This will create two tables   MonthGoals and QuarterGoals.

Month Goals contains the month name in Month_name and the goal in Month_goals.

Quarter Goals contains the quarter name in Quarter_name and the goal in Quarter_goals.

If you have more than one key field then use

Crosstable((Month_name, Month_goals, n)

where "n" is the number of key fields. Then list those fields first. For instance, if you had Department and Employee then you'd use

CrossTable (Month_name, Month_goals, 2)
Load
    Department,
     Employee,
    Jan20,
    Feb20,
    .
    .
    etc.....

I hope this helps. Check out my blog at QlikWithFriends.com.

Naresh1
Contributor III
Contributor III
Author

i have one key field and i want to show that into pivot table Catagory,week1,week2,week3,quarter_goals,monthgoals,month1,month2,month3

like this

could you please explain briefly

anushree1
Specialist II
Specialist II

Sample of your data with the desired outcome would be helpful to provide a solution