how to display selected yr 0 transactions

[kunkumnaveen]

Hello Everyone,

                        Can any one help me in  writing a expression for a requirement ,i need to show only those vendors and materials who is having nil transaction in selected year(Zero) and having transaction in selected previous year (expression need to be reflect  dynamically based up on selected year)

For example

vendor material  volume  year

xxx        aaa          0          2017

xxx        aaa        1234        2017

xxx        aaa        -1234      2017

yyy        bbb        100        2017

xxx        aaa        1000      2016

if a fiscal year 2017 is selected i need to display this in a table

vendor    material          selected year volume(2017)            selected previous year volume(2016)

    xxx        aaa                          0  (0+1234-1234=0)                          1000

vendor yyy has transaction in selected yr so this vendor should not display

thanks

naveen

[sunny_talwar]

Like this

Selected Year

Sum({<year = {$(=Max(year))}>}volume)

Selected Previous Year

Sum({<year = {$(=Max(year)-1)}, vendor = p(vendor)>}volume)

[arvind_patil]

Hi Naveen,

You might require two condition first for table have zero volume for current  selected period and

volume not zero for previous year.

1.SUM({<Employee = {"=aggr(SUM(volume),vendor ,material)<=0"},year={'(=max(year))'}>}volume)

2.SUM({<Employee = {"=aggr(SUM(volume),vendor ,material)>0"},year={'(=max(year)-1)'}>}volume)

Thanks,

Arvind Patil

[prat1507]

PFA the desired app

[kunkumnaveen]

hi ,thanks for your reply,can  u plz post those expression .....

[prat1507]

if(Sum({<year={'$(=GetFieldSelections(year))'}>}volume)=0,Sum({<year={'$(=GetFieldSelections(year))'}>}volume))

Sum({<year={'$(=GetFieldSelections(year)-1)'}>}volume)

[sunny_talwar]

Misread the requirement... may be this

Sum({<year = {$(=Max(year))}, vendor = {"=Sum({<year = {$(=Max(year))}>}volume) = 0"}>}volume)

Sum({<year = {$(=Max(year)-1)}, vendor = {"=Sum({<year = {$(=Max(year))}>}volume) = 0"}>}volume)

[kunkumnaveen]

hi sunny thanks for reply, if i need to show number of supplier

while this work

count({<year = {$(=Max(year)-1)}, vendor = p(vendor)>}vendors)

[rahulpawarb]

May be this:

//We are flagging records of year & vendor to identify zero transaction or not
Data:
LOAD * INLINE [
vendor, material, volume, year
xxx, aaa, 0, 2017
xxx, aaa, 1234, 2017
xxx, aaa, -1234, 2017
yyy, bbb, 100 , 2017
xxx, aaa, 1000, 2016
];
JOIN
LOAD year,
            vendor,
            If(yearly_volume=0, 1, 0) AS Flag;
LOAD year,
            vendor,
            Sum(volume) AS yearly_volume
Resident Data
Group By year, vendor;

Regards!

Rahul Pawar

[sunny_talwar]

I used an incorrect expression earlier... but whatever set analysis you use, I would do a DISTINCT Count instead of just counting the vendors to avoid double or triple counting the same vendor