Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 🙂
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
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')))))
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
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
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
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
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?
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