Solved: Re: creating a new field - Qlik Community

hamza99a · ‎2018-08-14

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

undergrinder · ‎2018-08-16

PLease see the attached screenshot

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

hamza99a · ‎2018-08-14

stalwar1‌ mahlerma‌ youssefbelloum‌ johnw‌ csg‌

undergrinder · ‎2018-08-15

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 · ‎2018-08-15

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 · ‎2018-08-15

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 · ‎2018-08-15

davidstorek‌ stalwar1‌ youssefbelloum‌ undergrinder‌

hamza99a · ‎2018-08-15

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

DavidŠtorek · ‎2018-08-16

Hello,

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

DavidŠtorek · ‎2018-08-16

Actualy it is the same

undergrinder · ‎2018-08-16

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.