topic Re: How to segregate data based on appreciation or depreciation ? in QlikView

How to segregate data based on appreciation or depreciation ?

shekhar_analyti — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to segregate data based on appreciation or depreciation ?

shekhar_analyti — Tue, 30 Jan 2018 10:20:12 GMT

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

Re: How to segregate data based on appreciation or depreciation ?

tresB — Tue, 30 Jan 2018 10:34:58 GMT

PFA

Re: How to segregate data based on appreciation or depreciation ?

shekhar_analyti — Tue, 30 Jan 2018 11:20:26 GMT

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

Re: How to segregate data based on appreciation or depreciation ?

tresB — Tue, 30 Jan 2018 11:52:15 GMT

What if you put a count condition like:

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

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

)

Re: How to segregate data based on appreciation or depreciation ?

shekhar_analyti — Tue, 30 Jan 2018 12:06:14 GMT

Hi Tresesco ,

Thanks . Above expression worked .

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

Re: How to segregate data based on appreciation or depreciation ?

tresB — Tue, 30 Jan 2018 12:14:06 GMT

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

Keywords : TOTAL, ALL & DISTINCT

What does the TOTAL qualifier do?

Re: How to segregate data based on appreciation or depreciation ?

shekhar_analyti — Tue, 30 Jan 2018 14:05:39 GMT

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 ?

Re: How to segregate data based on appreciation or depreciation ?

shekhar_analyti — Tue, 30 Jan 2018 14:43:40 GMT

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

Re: How to segregate data based on appreciation or depreciation ?

sunny_talwar — Tue, 30 Jan 2018 14:50:41 GMT

May be this

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