Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
VDCastro11
Partner - Contributor II
Partner - Contributor II

Pivot table with annual financial information

Hello everyone,

I need to create a simple financial table with the annual resume, something like this:

 

MonthTypeIncome (€)Outcome (€)Transport (€)
Begin Year   500
janMaterial 100500+300-100=700
Sales300 
fevFood 100700-600=500
mar   500
apr   500
may   500
jun   500
jul   500
aug   500
sep   500
nov   500
decSales500 500+500-30-50=920
Material 50
Food30 
End Year   920

 

My problem is in the development of the last colum

I have a variable to calculate the values of Income / Outcome and I tried to create the transport column by:

=$(vIncome) - $(vOutcome) - Sum({1<Year={$(vMaxYear)}, MonthNum = {1} >} [Value]) (To get my value at the begin of year - January)  

but I am getting transport values only when I have income/outcome values :

MonthTypeIncomeOutcomeTransport
Begin Year   500
janMaterial 100500+300-100=700
Sales300 
fevFood 100700-600=500
mar   -
apr   -
may   -
jun   -
jul   -
aug   -
sep   -
nov   -
decSales500 500+500-30-50=920
Material 50
Food30 
End Year   920

Is there any solution to "propagate" the same value of the begin of year (500) by all months to get the correct results?


And there is any solution to add the rows 'Begin Year' / 'End Year' to the table?

 

Thanks in advance

best regards

Labels (3)
1 Reply
Anil_Babu_Samineni

Your source is messy, can you create sample application to work for this use case?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful