How to segregate data based on appreciation or depreciation ?

[shekhar_analyti]

Hi All ,

I want to segregate data based on one field value (Index) , where for a given vendor for given time period either value is appreciated or

depreciated .

In attached sample i have Data and expected output .

Thanks & Regards

Shekar

[shekhar_analyti]

Note : User have two input boxes to select start year and end year .. And between these the above apprection or deprection is to be segregated

[tresesco]

PFA

[shekhar_analyti]

Hi Tresesco ,

Thanks for reply . But My expected output for Mvalue is

Vendors	Product	SubProduct	Year	Index	Value
Gintech	Solar Cells		2016	Mvalue	30
Gintech	Solar Cells		2016	Mvalue	38
Gintech	Solar Cells		2017	Mvalue	42
Vikram Solar	Solar Cells	Hedge	2017	Mvalue	22
Vikram Solar	Solar Cells	Hedge	2016	Mvalue	32
Primestone	Visuals	Spy Cam	2016	Mvalue	15
Primestone	Visuals	Spy Cam	2015	Mvalue	16

But in your screenshot i see L&T and Primestone

L&T is a single vendor for which there is single entry hence we cannot

asscertain whether its Mvalue is incerasing or decreasing

With Primestone Mvalue is for two different subproduct , but requirement

is for similar sub product and product

Similarly Vikram Solar has an extra entry with subproduct infra , while

only two entries of vikram solar should have been there of product Solar cell

and Subproduct Hedge

[tresesco · Accepted solution]

What if you put a count condition like:

If(Count(total <Vendors,Product, SubProduct> Value)>1,

          Max( {<Year={">=$(vStartYear)<=$(vEndYear)"}>}Value)

)

[shekhar_analyti]

Hi Tresesco ,

Thanks . Above expression worked .

Please help me understand : Count(total <Vendors,Product, SubProduct> Value)  . What Total is exactly doing ?

[tresesco]

Total is a qualifier in qlik that defines/manipulates the aggregation level in charts. For better understanding, check these links:

Keywords : TOTAL, ALL &amp; DISTINCT

What does the TOTAL qualifier do?

[shekhar_analyti]

Hi Tresesco ,

I find a issue with the above expression .

1) In straight table

The Moment i select 2016 as my start year the requirement takes beating , In below table Primestone Visuals Spy cam should not be in the table ... because it has no 2017 entry , so there is neither appreciation not depreciation can be estimated , hence this record should not have appeared in table

2) Pivot Table

How Can i have only those records for which value in both 2016 and 2017 is present ?

[shekhar_analyti]

Since Tresesco is busy ..... stalwar1‌ .. expertise needed

[sunny_talwar]

May be this

If(Count({<Year={">=$(vStartYear)<=$(vEndYear)"}>} total <Vendors,Product, SubProduct> Value)>1,Max( {<Year={">=$(vStartYear)<=$(vEndYear)"}>}Value))