Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
unkisoN
Contributor II
Contributor II

Bar and Line Chart Questions

Hi,

 

following example Table:

 

LOAD * INLINE [
F1, F2, F3, F4, F5, F6
Impl_Year, Impl_Number, SalesPerson, ItemCode, Forecast
2018, 2018_01, John, 100101, 25000
2018, 2018_02, Boris, 100102, n.a.
2018, 2018_03, Pablo, 100103, 20000
2019, 2019_01, John, 100104, 15000
2019, 2019_02, Boris, 100105, n.a.
2019, 2019_03, Pablo, 100106, 7500
];

LOAD * INLINE [
F1, F2, F3, F4, F5
PostingDate, ItemCode, InvoiceNumber, Amount, InvoiceType
01.01.2018, 100101, INV_01, 1550, 1
01.01.2018, 100101, INV_02, 30000, 2
01.01.2018, 100102, INV_03, 25000, 1
01.06.2018, 100102, INV_04, 18555, 2
01.06.2018, 100102, INV_05, 33445, 1
01.06.2018, 100103, INV_06, 25222, 2
01.06.2018, 100104, INV_07, 1520, 1
01.03.2019, 100106, INV_08, 1521, 2
01.03.2019, 100105, INV_09, 14582, 1
01.03.2019, 100103, INV_10, 12345, 2
01.03.2019, 100105, INV_11, 455, 1
];

 

I have created a pivot table with the following expressions and it works perfect so far.

 

Expression(Amount):

Sum({$< InvoiceType-={1}>} Amount

 

Expression (Fullfillment):
if(Forecast = 'n.a.', 'no forecast',
(Sum({$< InvoiceType-={1}>} Amount)/Forecast))

Expression (Classification):
if(Fullfillment= 'no forecast', 'no forecast',
if(Fullfillment > 1.50, '>150',
if(Fullfillment > 1.10, '110-150',
if(Fullfillment > 0.90, '90-110',
if(Fullfillment >= 0.50, '50-90', '<50')))))

Background Color:
if(Fullfillment= 'no forecast', RGB(191, 191, 191),
if(Fullfillment > 1.50, RGB(0,102,255),
if(Fullfillment > 1.10, RGB(0,176,80),
if(Fullfillment > 0.90, RGB(146,208,80),
if(Fullfillment >= 0.50, RGB(255,192,0), RGB(255,0,0))))))

 

 

Now i need two Charts:
counting the items which are sold by years and the classification as single bars

I've tried already a couple of ways but cannot get to the right solution.

unkisoN_0-1614002359815.png

 

2nd Chart I don't even know how to start:

I need to show the development of the Items based on the implementation Year and how long they are active:

 

unkisoN_1-1614002793368.png

 

I hope i have explained my issues understandable 🙂

 

 

7 Replies
edwin
Specialist III
Specialist III

if i understand correctly the first chart has the buckets as dimensions - just use the following expression as calculated dimension:

=if(aggr(Sum({$< InvoiceType-={1}>} Amount), Impl_Year)= 'no forecast', 'no forecast',
if(aggr(Sum({$< InvoiceType-={1}>} Amount), Impl_Year) > 1.50, '>150',
if(aggr(Sum({$< InvoiceType-={1}>} Amount), Impl_Year) > 1.10, '110-150',
if(aggr(Sum({$< InvoiceType-={1}>} Amount), Impl_Year) > 0.90, '90-110',
if(aggr(Sum({$< InvoiceType-={1}>} Amount), Impl_Year) >= 0.50, '50-90', '<50')))))
edwin
Specialist III
Specialist III

unfortunately, you can't reference your expression in your dimensions so you need to expand on it and you need to make it an AGGR to be allowed in dimensions

edwin
Specialist III
Specialist III

the 2nd requirement doesn't appear to be straight forward so you need to supply more info instead of leting everyone guess using the chart

unkisoN
Contributor II
Contributor II

Hi @edwin 

thanks for the reply, i will try and give you feedback.

 

Regarding 2nd requirement:

I have the following expression for every year:

Sum({$< InvoiceType-={1} , Year={2018}>} Amount

Sum({$< InvoiceType-={1} , Year={2019}>} Amount

 

This are the lines and this is working perfect already.

 

The Problem i have is the years.

For example ItemCode: 100103

It was sold the first time in 2018 and it was sold 2019 as well

ItemCode 100105 instead was implemented 2019 and therefore it was first sold in 2019

 

Now i need the comparison, how much of the ItemCode was sold in the first year of implementation and how the development is after the next years

 

I hope this explenation is good enough

 

edwin
Specialist III
Specialist III

i think the key is finding the first year for each item and creating a common dimension that levels the different dates.  look at the attached

unkisoN
Contributor II
Contributor II

Hi @edwin 

 

Solution 2 worked perfect !

Thank you.

 

About solution 1:

what do you mean with 

"you need to expand on it and you need to make it an AGGR to be allowed in dimensions"

 

You mean i should get the expression as aggr?

 

edwin
Specialist III
Specialist III

when building expressions you can refer to other expression as such:

if(Fullfillment= 'no forecast', 'no forecast',
if(Fullfillment > 1.50, '>150',
if(Fullfillment > 1.10, '110-150',
if(Fullfillment > 0.90, '90-110',
if(Fullfillment >= 0.50, '50-90', '<50')))))

where Fulfillment is another expression

but you really need this as a dimension.  in dimensions you can get away with creating  expressions but you have to use AGGR and you cant refer to defined expressions you have to expand on Fulfillment:

=if(aggr(Sum({$< InvoiceType-={1}>} Amount), Impl_Year)= 'no forecast', 'no forecast',
if(aggr(Sum({$< InvoiceType-={1}>} Amount), Impl_Year) > 1.50, '>150',
if(aggr(Sum({$< InvoiceType-={1}>} Amount), Impl_Year) > 1.10, '110-150',
if(aggr(Sum({$< InvoiceType-={1}>} Amount), Impl_Year) > 0.90, '90-110',
if(aggr(Sum({$< InvoiceType-={1}>} Amount), Impl_Year) >= 0.50, '50-90', '<50')))))

that is what i mean