Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
prat121
Contributor III
Contributor III
‎2023-09-21 05:33 AM

QLik

I have been given a dataset and I have been asked to create a measure table. below is the table and the measure list I have is this.... now please someone suggest me how can i create a measure table in the script

Revenue
Gross Profit
PBIT
ROCE
Working Capital
Account Receivables
Account Payable
Inventory
Total Cost
COGS
Operation Expenses
Manpower Cost
Net Profit
Operation Cost
Transportation Cost
Days Sales Outstanding
Inventory Days

 

 

 

 

 

Sr. No Business Unit Division Plant Material Group Product2 Vendor_Customer Purchasing Organization Bucket Date Measure Name Value Goal
1 BU 1 Div 1 Plant 1 Mrp Grp 1 Product 1 Customer 1 Org 1   01-01-2009 Revenue 1850 3975
2 BU 2 Div 2 Plant 1 Mrp Grp 1 Product 2 Customer 2 Org 2   01-01-2009 Revenue 5656 4621
3 BU 3 Div 3 Plant 1 Mrp Grp 1 Product 3 Customer 3 Org 3   01-01-2009 Revenue 5434 1627
4 BU 1 Div 4 Plant 1 Mrp Grp 2 Product 4 Customer 4 Org 4   01-01-2009 Revenue 2947 2445
5 BU 2 Div 5 Plant 2 Mrp Grp 2 Product 5 Customer 5 Org 5   01-01-2009 Revenue 1974 3287
Labels (1)
Labels
745 Views
9 Replies
prat121
Contributor III
Contributor III
‎2023-09-21 05:35 AM
Author

Sr. No Business Unit Division Plant Material Group Product2 Vendor_Customer Purchasing Organization Bucket Date Measure Name Value Goal
1 BU 1 Div 1 Plant 1 Mrp Grp 1 Product 1 Customer 1 Org 1   01-01-2009 Revenue 1850 3975
2 BU 2 Div 2 Plant 1 Mrp Grp 1 Product 2 Customer 2 Org 2   01-01-2009 Revenue 5656 4621
3 BU 3 Div 3 Plant 1 Mrp Grp 1 Product 3 Customer 3 Org 3   01-01-2009 Revenue 5434 1627
4 BU 1 Div 4 Plant 1 Mrp Grp 2 Product 4 Customer 4 Org 4   01-01-2009 Revenue 2947 2445
5 BU 2 Div 5 Plant 2 Mrp Grp 2 Product 5 Customer 5 Org 5   01-01-2009 Revenue 1974 3287
6 BU 3 Div 6 Plant 2 Mrp Grp 3 Product 6 Customer 6 Org 6   01-01-2009 Revenue 3905 2269
7 BU 1 Div 1 Plant 2 Mrp Grp 3 Product 7 Customer 7 Org 7   01-01-2009 Revenue 4786 2925
741 Views
0 Likes
Or
MVP
MVP
‎2023-09-21 05:36 AM

Not sure quite what you mean by "Create a measure table". You can just create a regular table with whatever measures you want. In your above example, it looks like a basic Sum(Revenue).

740 Views
0 Likes
prat121
Contributor III
Contributor III
‎2023-09-21 05:41 AM
Author

In response to Or

what about other measures ive many measures there, do i have to calculate different measures everytime using a resident load.

732 Views
0 Likes
Or
MVP
MVP
‎2023-09-21 05:47 AM

In response to prat121

Typically something like this would be calculated on the front end, not in the script. Doing it in the script will your row count being 16 times higher, which isn't likely to be the best approach.

If you do it in script, you can join the two tables to create a Cartesian join, and then resident load adding the appropriate expressions using Pick(Match()) or nested If() statements.

723 Views
0 Likes
prat121
Contributor III
Contributor III
‎2023-09-21 05:50 AM
Author

In response to Or

yes I know this is not the best approach but I have been given this as an assignment, now I guess i will have to do this using the functions, like you said and create multiple tables for every measure.

713 Views
0 Likes
Or
MVP
MVP
‎2023-09-21 05:59 AM

In response to prat121

Weird to get an assignment that specifies you have to do something in this exact manner, but if that's what you got, good luck I guess!

704 Views
0 Likes
prat121
Contributor III
Contributor III
‎2023-09-21 06:00 AM
Author

In response to Or

Yes, actually I have been Given a Power BI wireframe as the requirements are like 

Tables that we need to use on this dashboard

  • Finance Data
  • Date Table
  • Measure Table

Data Model

 

  • Create a Relationship between Finance Data and Date Table
  • Create a Relationship between Finance Data and Measure Table

Expected Functionality

  • The Header should contain the company logo, header, and User Name (First Name and Last Name)
  • The Next header should be the Page header containing the Executive Summary, The Dropdown should consist of a Period filter which should be in mmm-yy also the last refreshed date should be today's date.
  • There are different KPI Tile each functionality should have as below:
    • The KPI Tile is divided into 4 parts
      • Previous Month – The data below the calendar icon in the previous month is based on the selection of the sub-header
      • The green icon and red icon should be changed based on the logic that it should be green if the previous month is less than the current month and if the previous month is greater than the current month it should be the red icon.
      • Revenue – The data here consists of the current month.
      • Goal – The goal value should be for the current month and the icon should be green when the goal is less than the current month and vice versa should be red.
      • There should be a trend line whose association should be removed from the period filter.
    • The above functionality is true in all the KPI Tiles.

      so this is what the requirement is like
698 Views
0 Likes
Or
MVP
MVP
‎2023-09-21 06:02 AM

In response to prat121

It feels like whoever wrote that has never used Qlik... not sure why anyone would insist on a weird data structure instead of designing for the required results. In any case, join the tables and then pick/match for the formulas in a resident load is the easiest approach for doing in script, probably.

695 Views
0 Likes
prat121
Contributor III
Contributor III
‎2023-09-21 07:34 AM
Author

In response to Or

Thank you for this

643 Views
0 Likes