Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

kunkumnaveen
Contributor III

Zero amount in selected FY and some amount in selected previous FY

Hello All,

I need a suggestion in achieving the below requirement,what i need to show is ,for selected fiscal Year show only

those vendors who spend is ZERO and has some spend in selected previous year. what i mean is from the below sample table


Fiscal Year      Vendor      Amount

2016               AAA         100

2016               AAA         200

2016               BBB         300

2016               CCC         50

2017               AAA        -50

2017               AAA         50

2017               BBB         100

2017               BBB         -100

2017               CCC          1000

If i select Fiscal Year :2017


OutputSmiley Frustratedelect

Vendor      Selected year Amount(2017)          Previous Year Amount(2016)

AAA                  0(50-50)                              300(100+200)

BBB                  0(100-100)                             300

Only those two vendors need to display because CCC has some spend in 2017 and has spend in 2016.....



Thanks

4 Replies
kunkumnaveen
Contributor III

Re: Zero amount in selected FY and some amount in selected previous FY

Any Suggestion Please.......

kunkumnaveen
Contributor III

Re: Zero amount in selected FY and some amount in selected previous FY

HI All, i am still looking for help for above requirement since yesterday,any suggestion plz

shraddha_g
Honored Contributor III

Re: Zero amount in selected FY and some amount in selected previous FY

For CurrentYear

Sum({<FiscalYear = {"$(=max(FiscalYear))"}>} Amount)

For Previous Year

Sum({<FiscalYear = {"$(=max(FiscalYear)-1)"}>} Amount)

Use these both in Measures and Vendor as Dimension

(Use Field Names According to your Script)

kunkumnaveen
Contributor III

Re: Zero amount in selected FY and some amount in selected previous FY

HI thanks for your reply, i think u haven't understand the requirement

For selected fiscal Year show only

those vendors who spend is ZERO and has some spend in selected previous year. what i mean is from the below sample table


According to your expression it will show all the vendors including CCC , but according to my requirement that particular vendor should  omitted