Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
hamza99a
Contributor 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
Highlighted
undergrinder
Valued Contributor II

Re: creating a new field

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
Highlighted
hamza99a
Contributor II

Re: creating a new field

Highlighted
undergrinder
Valued Contributor II

Re: creating a new field

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.

Highlighted
balabhaskarqlik
Honored Contributor

Re: creating a new field

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])

Highlighted
hamza99a
Contributor II

Re: creating a new field

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.

Highlighted
hamza99a
Contributor II

Re: creating a new field

Highlighted
hamza99a
Contributor II

Re: creating a new field

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

Highlighted
DavidŠtorek
Contributor III

Re: creating a new field

Hello,

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

Highlighted
DavidŠtorek
Contributor III

Re: creating a new field

Actualy it is the same

Highlighted
undergrinder
Valued Contributor II

Re: creating a new field

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.