Bar and Line Chart Questions

[unkisoN]

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.

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:

I hope i have explained my issues understandable 🙂

[edwin]

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]

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]

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]

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]

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]

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 · Accepted solution]

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