topic How to continue calculating with results in QlikView

How to continue calculating with results

MBV — Sat, 16 Nov 2024 19:21:19 GMT

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).

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!

Re: How to continue calculating with results

Brett_Bleess — Mon, 06 Jan 2020 18:44:37 GMT

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/ba-p/1468702

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

Re: How to continue calculating with results

Kushal_Chawda — Mon, 06 Jan 2020 22:03:32 GMT

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

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>')