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: 
hamza99a
Creator II
Creator II

creating a new field

Hello all,

I have created a table that shows Account Type, the region, and the balance amount for the day. I now want to show percentage of my yearly goal for each region and each account type. I have created an excel file that has the same dimension names, yet when I add them in and try to do a calculation with the goal, it seems that it appears null. If anyone knows how to help Id greatly appreciate it.

Thanks,

Hamza

1 Solution

Accepted Solutions
undergrinder
Specialist II
Specialist II

PLease see the attached screenshot

goal-fact.png

You need proper create the data model.

My load script was based on your sample:

Goals:

LOAD

    upper("Goals Branch Region" &'-'& GoalsGovAccType) as Key,

    "GOALS:" as Goals

FROM [lib://download/Sample data Goals (1).xlsx]

(ooxml, embedded labels, table is Sheet1);


Facts_tmp:

LOAD

    "ACCT NBR",

    GovAccType,

    "Current Balance",

    "Production Date",

    "Tax ID",

    "Balance Previous Year",

    "Open DT",

    "Close DT",

    "Branch Number"

FROM [lib://download/Sample Datareal.xlsx]

(ooxml, embedded labels, table is Deposits28);


Left join(Facts_tmp)

LOAD

    "Branch Region",

    "Branch Number"

FROM [lib://download/Sample Datareal.xlsx]

(ooxml, embedded labels, table is Branch);


Facts:

Load

    "ACCT NBR",

    GovAccType,

    "Current Balance",

    "Production Date",

    "Tax ID",

    "Balance Previous Year",

    "Open DT",

    "Close DT",

    "Branch Number",

    "Branch Region",

    upper("Branch Region" & '-' & GovAccType) as Key

Resident Facts_tmp;


drop table Facts_tmp;

G.

View solution in original post

23 Replies
hamza99a
Creator II
Creator II
Author

undergrinder
Specialist II
Specialist II

hi,

could you provide us an example?

Have you already got a year-to-date data or this is also part of the task?

G.

balabhaskarqlik

Provide the expression or a screen shot...

Or

Try like this:

Sum({< Year = {'2018'},[Account type]=,Region= >} [Balance Amount])/Sum(total {< Year = {'2018'},[Account type]=,Region= >} [Balance Amount])

hamza99a
Creator II
Creator II
Author

I currently have a vizlib sparkline table organized by branch region, then govACC Type. I would like to input goals into the table so that I can use it for comparison purposes. I created some sample data of the deposits table and the goals table that I had just created. For some reason the goals are not making the connection into the table. I also cannot use if statements in the table as it makes the sparklines disappear. I also tried to left join the tables based on branch numbers, but that caused qlik sense to crash. Any help would be greatly appreciated.

hamza99a
Creator II
Creator II
Author

hamza99a
Creator II
Creator II
Author

I even tried adding in the goals table manually and I get this error. stalwar1‌ please help

DavidŠtorek
Creator III
Creator III

Hello,

try this post % of Goal it is the simmilar to what you are asking

DavidŠtorek
Creator III
Creator III

Actualy it is the same

undergrinder
Specialist II
Specialist II

Hi Hamza,

I checked the attached files,

The goals and the facts can connect though GovAccType - GoalsGovAccType column - did you rename these columns to share the name?

And the Branch number should be changed to Goals Branch Region or vice versa, to fulfill the key's requirements.

G.