Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
Can someone please help me realize Qlik Sense Table as shown in screenshot and attached excel ?
I want to add calculated rows but looks like nothing is working.
Thanks
@syedsaqib always between current year and the next year ?
change in the same table ?
Hi @syedsaqib
Based in the definition of your problem, they way your data is organized in Excel makes a bit more challenging to implement in Qlik; your example includes 3 periods, and two years (2020 and 2021) without more info, and with the structure shared in Excel, I got the following result (see screenshot):
as you can notice in the screenshot, I re-organized your Excel file layout (actually, the Qlik's Load Script did that); the solution implements the Generic prefix; to unpack your tall table, the Generic prefix (command) allows us to show your measures (Margin, Orders, Revenue and Selling Expense) as column, having Year-Period and Year as dimension!
The attached Qlik Solution (Z_Excel_Formulas.QVF) contains a solution to your question; now the benefits of this solutions are:
Hope this helps,
Thanks ArnadoSandoval, I am unable to import the app, not sure why.?
Hi Taoufiq,
Thanks for reply.
"Change" column will always calculate difference between "Current Year" and "Previous Year" (2020 vs 2021)
and,
Calculated Row
Calculated Row: Gross Margin% = (Gross Margin)/(Revenue)
Calculated Row: Selling Expense % = (Selling Expense)/(Revenue)
Thanks!
Hi @syedsaqib
I do not know either, anyhow, I am attaching a new version of the same application, I removed my data connection and commented out some STORE / INTO statements, I will also include its script here, as most of your issue is resolved in the script by implementing a GENERIC prefix.
These are the load script sections:
Main: (the date formatting lines 7 and 8 are set to D/M/Y)
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='D/M/YYYY';
SET TimestampFormat='D/M/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';
Load Sample & KPI tables: (Load your sample data, order it, apply the Generic prefix and drop the temporary table)
Sample:
LOAD
"Year" & '|' & Period As Year_Period,
KPI,
Act
FROM [lib://Sample_Data/Sample data.xlsx]
(ooxml, embedded labels, table is Sheet1);
NoConcatenate
Sample_Temp:
Load Year_Period,
KPI,
Act
Resident Sample
Order By Year_Period,
KPI;
// the generic load creates as many tables as kpis
// your example will generate 4 tables
kpi:
Generic Load
Year_Period,
KPI,
Act
Resident Sample_Temp;
Drop Table Sample_Temp;
// we create a qvd for each one of this tables,
// they are to follow-up and validate the logic
// it also helps us to understand how generic works
// Store kpi.Margin into [lib://Sample_Data/kpi.margin.qvd];
// Store kpi.Orders into [lib://Sample_Data/kpi.orders.qvd];
// Store kpi.Revenue into [lib://Sample_Data/kpi.revenue.qvd];
// Store kpi.SellingExpense into [lib://Sample_Data/kpi.sellingExpense.qvd];
Merged KPI Table: (The Generic prefix creates tables per KPI group, e.g. Margin, Orders, Revenue, Selling Expense; Qlik Help Example put their data together with Left Joins, which did not work for me, so I concatenate these tables instead
// the left-join implementation is by adapting
// QlikSense example, but it does not work
// with your data, these joins lost their
// bearings generating un-expected records
// that's the reason why we commented out them
/*
Left Join (kpi.Margin)
Load Orders Resident kpi.Orders;
Left Join (kpi.Margin)
Load Revenue Resident kpi.Revenue;
Left Join (kpi.Margin)
Load SellingExpense Resident kpi.SellingExpense;
*/
// Rename columns to avoid synthetic keys
NoConcatenate
KPIs_temp:
Load * Resident kpi.Margin;
Concatenate Load * Resident kpi.Orders;
Concatenate Load * Resident kpi.Revenue;
Concatenate Load * Resident kpi.SellingExpense;
Store KPIs_temp into [lib://Sample_Data/kpis_temp.qvd];
NoConcatenate
KPIs:
Load Year_Period As k_Year_Period,
SubField(Year_Period,'|', 1) As Year,
Sum(Margin) As Margin,
Sum(Orders) As Orders,
Sum(Revenue) As Revenue,
Sum(SellingExpense) As SellingExpense
Resident KPIs_temp
Group By
Year_Period,
SubField(Year_Period,'|', 1);
// Store KPIs into [lib://Sample_Data/kpis.qvd];
Drop Table KPIs_temp;
Clean UP: (I create the kpi_xxxxx tables to illustrate the way the generic prefix work, and to present the data in the UI, you may not need them, the script also drop the tables: kpi.Margin; kpi.Orders; kpi.Revenue and kpi.SellingExpenses; note: these tables are automatically generated by the Generic Load prefix)
// We do not need to keep the four kpis tables
// kpi.Margin
// kpi.Orders
// kpi.Revenue
// kpi.SellingExpense
// but because the synthetic keys between them,
// it cripples showing them in the UI,
// so here we break those synthetic keys
NoConcatenate
kpi_Margin:
Load Year_Period As m_Year_Period,
Margin As m_Margin
Resident kpi.Margin;
NoConcatenate
kpi_Orders:
Load Year_Period As o_Year_Period,
Orders As o_Orders
Resident kpi.Orders;
NoConcatenate
kpi_Revenue:
Load Year_Period As r_Year_Period,
Revenue As r_Revenue
Resident kpi.Revenue;
NoConcatenate
kpi_SellingExpense:
Load Year_Period As se_Year_Period,
SellingExpense As se_SellingExpense
Resident kpi.SellingExpense;
// we are dropping the kpi.xxx tables as we are done
// renaming their columns to get rid of synthetic keys
drop table kpi.Margin;
drop table kpi.Orders;
drop table kpi.Revenue;
drop table kpi.SellingExpense;
Exit Script:
Exit Script;
I hope that the Qlik Community interface does not damage the qvf file this time, BTW I am using the November 2020 version of Qlik.
hth