Calculated row and column in same Table

[syedsaqib]

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

[Taoufiq_Zarra]

@syedsaqib  always between current year and the next year ?

change in the same table ?

[ArnadoSandoval]

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:

• It is flexible, it could accommodate additional measures with minimum impact in the code.
• It has not limitation handling additional financial years and periods.
• Even thought the KPIs are hardcode, you can enhance the code to calculate them via a selector on the UI.
• I added lots of comments to the Load Script so you can follow up on the implementation of the Generic Prefix.

Hope this helps,

[syedsaqib]

Thanks ArnadoSandoval, I am unable to import the app, not sure why.?

[syedsaqib]

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!  

[ArnadoSandoval]

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