Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
i need to show the list of all suppliers associated with a part but with nil transactions in the chosen period.let say i have sample data
year Supplier part spend
2017 xxx aaa 100
2017 yyy bbb 200
2017 zzz ccc 0
2017 zzz ccc 100
2017 vvv ddd 0
2016 yyy bbb 0
2016 zzz ccc 100
2016 zzz ccc 0
2016 vvv ddd 20
2015 yyy bbb 10
2015 zzz ccc 100
2015 zzz ccc 50
2015 vvv ddd 20
and if i choose 2017 so i need see below data(suppliers current year spend is nil and having some spend in previous year )
supplier part currentyear(2017) previousyear(2016)
zzz ccc 0 100
vvv ddd 0 20
if i choose 2016 so i need see below data
supplier part currentyear(2016) previousyear(2015)
yyy bbb 0 10
zzz ccc 0 150 (100+50)
I have not clue how to write a expression to achieve this requirement ,can any suggestion plz
thanks
For Prev Year.
Sum({<Supplier =p({<spend = {"0"}>}),year = {"$(=max(year)-1)"}>}spend)
For Current Year
Sum({<spend = {"0"}>}spend)
Regards,
Kaushik Solanki
Hi,
You can have 2 expression
one is if(sum(Spend)=0,sum(Spend))
2nd is sum({<Year={$(=Year-1)}>}Spend)
But in your data there is
2017 zzz ccc 0
2017 zzz ccc 100
So what should be the result?
Thanks
Ravi Balar
when you select 2017 what will happen to XXX & YYY?
Hi,
Try this expression.
Sum({<Supplier =p({<spend = {"0"}>}),year = {"$(=max(year)-1)"}>}spend)
See attached application.
Regards,
Kaushik Solanki
HI ,it should not display those suppliers because there spend is not zero
the result is
supplier part currentyear(2017) previousyear(2016)
zzz ccc 0 100
vvv ddd 0 20
it should consider only this
2017 zzz ccc 0
then use below formula's
First 1: if(isnull(FirstSortedValue(Aggr(sum(Spend),Supplier,Part,Year),1)) or FirstSortedValue(Aggr(sum(Spend),Supplier,Part,Year),1)=0,0)
Second: if(Column(1)=0,sum({<Year={$(=Year-1)}>}Spend))
Regards,
Ravi Balar
Put the below expressions
Current Year:
Sum({<spend={"=0"}>}spend)
As suggested by Kaushik
Previous Year:
Sum({<Supplier =p({<spend = {"0"}>}),year = {"$(=max(year)-1)"}>}spend)
Just observation, I think he is looking for current year value along with previous year value.
I tried with your solution but it is giving me sum of all values.
Ravi,
Have a look at the attached application. That will give you clear picture.
Regards,
Kaushik Solanki