Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

kunkumnaveen
Contributor III

how to display selected yr 0 transactions

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

11 Replies

Re: how to display selected yr 0 transactions

Like this

Selected Year

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

Selected Previous Year

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

arvind_patil
Valued Contributor II

Re: how to display selected yr 0 transactions

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
Valued Contributor

Re: how to display selected yr 0 transactions

PFA the desired app

kunkumnaveen
Contributor III

Re: how to display selected yr 0 transactions

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

prat1507
Valued Contributor

Re: how to display  selected yr 0 transactions

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

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

Re: how to display selected yr 0 transactions

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
Contributor III

Re: how to display selected yr 0 transactions

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
Valued Contributor II

Re: how to display selected yr 0 transactions

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

Re: how to display selected yr 0 transactions

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

Community Browser