Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Learner
Contributor III
Contributor III

How to insert row and calculate value based on above two rows

Hello,

I want to calculate insert two rows and calculate EBITDA Rs/T & EBITDA Rs Cr. based on NSR & Cos. My data is as follows:

UnitGroupCHDGGBS
DLVProduction_Sales qty.100005000
DLVNSR Part50002000
DLVCos4000900
NDLProduction_Sales qty.110006000
NDLNSR Part51002100
NDLCos4200950

 

I have made App like this:

UnitGroupCHDGGBS
DLVProduction_Sales qty.100005000
 NSR Part50002000
 Cos4000900
NDLProduction_Sales qty.110006000
 NSR Part51002100
 Cos4200950

 

What i want is the yellow lines as shown below:

Learner_0-1613567792448.png

EBITDA Rs/T is Row no. 2 less row no. 3 in Unit DLV same for NDL unit.

EBITDA Rs Cr. is Row no. 4 * Row no. 1 in Unit DLV same for NDL unit.

Please help me out in the above report......

with regards,

Ashish

 

 

2 Solutions

Accepted Solutions
QFabian
Specialist II
Specialist II

Hi @Learner , please see if this is an acceptable solution for you :

QFabian_0-1613571414740.png

 

The final number for 'EBITDA Rs Cr.' is the unique difference, please check

DataX:
LOAD * INLINE [
Unit, Group, CHD, GGBS
DLV, Production_Sales qty., 10000, 5000
DLV, NSR Part, 5000, 2000
DLV, Cos, 4000, 900
NDL, Production_Sales qty., 11000, 6000
NDL, NSR Part, 5100, 2100
NDL, Cos, 4200, 950
];

Data:
Load
AutoNumber(Group, Unit) as Id,
Unit,
Group,
CHD,
GGBS
Resident DataX;
drop table DataX;
concatenate
Load distinct
Unit,
4 as Id,
'EBITDA RS / T' as Group
Resident Data;
concatenate
Load distinct
Unit,
5 as Id,
'EBITDA Rs Cr.' as Group
Resident Data;

Data2:
Load
1 as Id2,
Unit,
Group,
Id,
if(Id = 4, previous(previous(CHD)) - previous(CHD),
if(Id = 5, peek(CHD2) / previous(previous(previous(previous(CHD)))), CHD)) as CHD2,

if(Id = 4, previous(previous(GGBS)) - previous(GGBS),
if(Id = 5, peek(GGBS2) / previous(previous(previous(previous(GGBS)))), GGBS)) as GGBS2

Resident Data
order by
Unit,
Id;
drop table Data;

QFabian

View solution in original post

marcus_sommer
MVP & Luminary
MVP & Luminary

Sorry, I mistake the parameter-order of pick() - therefore change it to:

concatenate(DataX)
load
pick(recno(), 'EBITDA1', 'EBITDA2') as Group autogenerate 2;

- Marcus

View solution in original post

6 Replies
QFabian
Specialist II
Specialist II

Hi @Learner , please see if this is an acceptable solution for you :

QFabian_0-1613571414740.png

 

The final number for 'EBITDA Rs Cr.' is the unique difference, please check

DataX:
LOAD * INLINE [
Unit, Group, CHD, GGBS
DLV, Production_Sales qty., 10000, 5000
DLV, NSR Part, 5000, 2000
DLV, Cos, 4000, 900
NDL, Production_Sales qty., 11000, 6000
NDL, NSR Part, 5100, 2100
NDL, Cos, 4200, 950
];

Data:
Load
AutoNumber(Group, Unit) as Id,
Unit,
Group,
CHD,
GGBS
Resident DataX;
drop table DataX;
concatenate
Load distinct
Unit,
4 as Id,
'EBITDA RS / T' as Group
Resident Data;
concatenate
Load distinct
Unit,
5 as Id,
'EBITDA Rs Cr.' as Group
Resident Data;

Data2:
Load
1 as Id2,
Unit,
Group,
Id,
if(Id = 4, previous(previous(CHD)) - previous(CHD),
if(Id = 5, peek(CHD2) / previous(previous(previous(previous(CHD)))), CHD)) as CHD2,

if(Id = 4, previous(previous(GGBS)) - previous(GGBS),
if(Id = 5, peek(GGBS2) / previous(previous(previous(previous(GGBS)))), GGBS)) as GGBS2

Resident Data
order by
Unit,
Id;
drop table Data;

QFabian

View solution in original post

Learner
Contributor III
Contributor III
Author

Many Thanks...... it does work......

But  is there any way to do this in front end i.e. in Expression and Dimension. we can add extra row and write formula as row no. 3 minus row no. 2. I am asking this because if there are lots of Columns & row than it will be difficult for write formula for each column(as in the example there are only 2 columns CHD & GGBS).

 

Thanking you,

 

Ashish

marcus_sommer
MVP & Luminary
MVP & Luminary

You don't need to calculate all values within the script but you need to add all artificial/extra dimension-values for all totals respectively intermediate calculations which aren't feasible with the partial sums within the object.

This means in your case something like:

concatenate(YourTable) load pick('EBITDA1', 'EBITDA2', recno()) as Group autogenerate 2;

adding just the dimension-values to your field. If they are there you could query them within your chart expressions like:

if(Group = 'EBITDA1', expr1, Group = 'EBITDA2', expr2, expr3))

If there are more as two such special calculations it becomes useful if there are flag-values which indicates if certain values are positive or negative to avoid further if-loops querying and branching for this information.

A step further in this logic would be to integrate the extra dimension-values within an The As-Of Table - Qlik Community - 1466130 which would avoid any branching and a simple sum(value) would be enough - unless by any rate-calculations which I wouldn't mix with the sums else applying it within an extra column.

- Marcus

Learner
Contributor III
Contributor III
Author

Thank you for your response.......

I tried typing the concatenate function in script but ,as i am new to scripting,  it did not worked may be i have written incorrectly. Here is what i wrote:

DataX:
LOAD * INLINE [
Unit, Group, CHD, GGBS
DLV, Production_Sales qty., 10000, 5000
DLV, NSR Part, 5000, 2000
DLV, Cos, 4000, 900
NDL, Production_Sales qty., 11000, 6000
NDL, NSR Part, 5100, 2100
NDL, Cos, 4200, 950
];

concatenate(DataX)
load
pick('EBITDA1', 'EBITDA2', recno()) as Group autogenerate 2;

 

but no extra row was created.

Learner_0-1613662278967.png

Screen shot of Chart & list box both.(EBITDA1 & EBITDA2 does not appears)

 

Please let me know where i am making mistake.

 

Thanking you,

Ashish

 

 

 

marcus_sommer
MVP & Luminary
MVP & Luminary

Sorry, I mistake the parameter-order of pick() - therefore change it to:

concatenate(DataX)
load
pick(recno(), 'EBITDA1', 'EBITDA2') as Group autogenerate 2;

- Marcus

View solution in original post

Learner
Contributor III
Contributor III
Author

Thank you it worked...............