Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
New Contributor II

How to continue calculating with results

Hey Guys,

I'm very new to QV. Sorry if this is a very simple question.

I calculated the sums of the types of cost in QV (left).

Unbenannt.PNG

Now I want to continue calculatig with the calculated values (total in straight table) to get a P&L Statement (right). Is that possible?

Please help me how to calculated it and how to display it (pivot-table, straight table?).

 

Thank you very much!

2 Replies
Highlighted
Digital Support
Digital Support

Re: How to continue calculating with results

Have a look at the following Design Blog post, I think it may help, there is an Extension for Sense, but not QlikView...

https://community.qlik.com/t5/Qlik-Design-Blog/How-IntervalMatch-Solved-My-Profit-and-Loss-Dilemma/b...

If you want to do some further searching in the Design Blog area yourself, there are hundreds of mostly how-to related things there, here is the URL to do that:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Sorry I do not have anything better for you, but hopefully this may get you on a decent path with things.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Highlighted

Re: How to continue calculating with results

Map_Description:
mapping LOAD * Inline [
Type of cost, Description
2400000,Gross Sales
2990000,Gross Sales
4761000,Sales Return
4769100,Cost of Sales
4700000,Cost of Sales
4700010,Warehousing
4900000,Freight & Delivery ];

Description:
LOAD * Inline [
Description, Number
Gross Sales,1
Sales Return,2
Cost of Sales,4
Freight & Delivery,5
Warehousing,6 ];

Data:
Load *,ApplyMap('Map_Description',[Type of cost],Null()) as Description Inline [
Type of cost, Value
2400000,1990
2990000,0
4700000,-220
4700010,-900
4761000,350
4762000,60
4764000,1000
4769100,-800
4770010,-50
4900000,-325 ];

Dim:
LOAD * Inline [
Dim
1
2
3
4 ]

 

Dimension

Heading-
=Pick(Dim,Description,'NET SALES','TOTAL COST OF SALES','GROSS MARGIN')
Note - Check option 'Suppress when value in NULL' for Heading

Type of Cost -
=Pick(Dim,[Type of cost],'','','')

 

Expression:

=Pick(Dim,
Sum(Value),
Sum({<Number={1,2}>}Value),
Sum({<Number={4,5,6}>}Value),
sum(Value))

 

Go to Straight table properties ->Presentation-> Uncheck 'Suppress zero values' 

 

Sort setting for heading is below

Capture.JPG

 

For Dimension Header Text Color

=if(Pick(Dim,Description,'NET SALES','TOTAL COST OF SALES','GROSS MARGIN')=
above(total Pick(Dim,Description,'NET SALES','TOTAL COST OF SALES','GROSS MARGIN')),White())

 

For Dimension Header & Measure Text Format

=if(Pick(Dim,Description,'NET SALES','TOTAL COST OF SALES','GROSS MARGIN')='NET SALES' or
Pick(Dim,Description,'NET SALES','TOTAL COST OF SALES','GROSS MARGIN')='TOTAL COST OF SALES' or
Pick(Dim,Description,'NET SALES','TOTAL COST OF SALES','GROSS MARGIN')='GROSS MARGIN','<b>')

 

Capture.JPG