

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to segregate data based on appreciation or depreciation ?
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
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What if you put a count condition like:
If(Count(total <Vendors,Product, SubProduct> Value)>1,
Max( {<Year={">=$(vStartYear)<=$(vEndYear)"}>}Value)
)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
PFA


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What if you put a count condition like:
If(Count(total <Vendors,Product, SubProduct> Value)>1,
Max( {<Year={">=$(vStartYear)<=$(vEndYear)"}>}Value)
)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Tresesco ,
Thanks . Above expression worked .
Please help me understand : Count(total <Vendors,Product, SubProduct> Value) . What Total is exactly doing ?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Total is a qualifier in qlik that defines/manipulates the aggregation level in charts. For better understanding, check these links:


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Since Tresesco is busy ..... stalwar1 .. expertise needed

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be this
If(Count({<Year={">=$(vStartYear)<=$(vEndYear)"}>} total <Vendors,Product, SubProduct> Value)>1,Max( {<Year={">=$(vStartYear)<=$(vEndYear)"}>}Value))
