topic Re: only min value in pivot in QlikView

only min value in pivot

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

Hi Folks,

i have a situation:

Year:

2016 300 €

2015 200 €

2014 100 €

How can i make visible on pivot-table this structure; i want to ignore the values 300€ and 200€ and show only 100 €

Year

2016 0 €

2015 0 €

2014 100 €

Thanks a lot

beck

Re: only min value in pivot

sunny_talwar — Mon, 13 Mar 2017 16:13:39 GMT

You want to show min year value or min value? If the 100 was next to 2015 and 200 was next to 2014, would you have shown 100 for 2015 or 200 for 2014?

Re: only min value in pivot

beck_bakytbek — Mon, 13 Mar 2017 16:18:05 GMT

200 for 2014

Thanks Sunny

Re: only min value in pivot

sunny_talwar — Mon, 13 Mar 2017 16:29:38 GMT

May be like this:

Sum({<Year = {"$(=Min({1}Year))"}>}Value)

Re: only min value in pivot

beck_bakytbek — Mon, 13 Mar 2017 17:06:31 GMT

Sunny Thanks a lot for your Feedback,

i have a question, can i use such expression with cumulated values?, and can i show show min of cumulated values

thanks a lot

beck

Re: only min value in pivot

sunny_talwar — Mon, 13 Mar 2017 17:14:18 GMT

Can you share an example of what you mean?

Re: only min value in pivot

beck_bakytbek — Mon, 13 Mar 2017 17:19:16 GMT

of course Sunny,

Thanks a lot

Re: only min value in pivot

sunny_talwar — Mon, 13 Mar 2017 17:21:56 GMT

What is the expected output you are looking to get?

Re: only min value in pivot

beck_bakytbek — Mon, 13 Mar 2017 17:33:55 GMT

i expect a min value on the base of cumulated value

Re: only min value in pivot

sunny_talwar — Mon, 13 Mar 2017 17:36:37 GMT

I sort of understand, but what is this value in the attached qvf? What are you expecting to see in your chart? What Year and what value next to it?

Re: only min value in pivot

beck_bakytbek — Tue, 14 Mar 2017 08:19:59 GMT

Hi Sunny,

sorry for my late response, my issue is: i want to see the calculated values and if it possible, to see the minimum of calculated value

thanks a lot for you help

beck

Re: only min value in pivot

sunny_talwar — Tue, 14 Mar 2017 09:21:21 GMT

I am looking for numerical numbers you expect to see and not the description. Would you be able to give me the numbers you are hoping to see my friend?

Re: only min value in pivot

beck_bakytbek — Tue, 14 Mar 2017 10:11:28 GMT

Hi Sunny,

thanks a lot for your responce, i made a mistake: under calculated values : i mean a cumulated value.

pleas look at attached screenshots, i want to see the cumulated values and if i select both year: for instance: 2014 and 2013, i want to see only 100 (cumulated values) within a straight table /pivot

i hope i explained well,

thanks a lot for your help

Re: only min value in pivot

sunny_talwar — Tue, 14 Mar 2017 10:14:07 GMT

If you select 2015 and 2016, what would you want to see? 600 (value accumulated since 2013 or 300 (where accumulation will start from 2015)?

Re: only min value in pivot

beck_bakytbek — Tue, 14 Mar 2017 10:18:29 GMT

if i select 2015 i want to see only 600

Re: only min value in pivot

sunny_talwar — Tue, 14 Mar 2017 10:32:26 GMT

Try this:

Sum({<Year = {$(=Min(Year))}>}Aggr(RangeSum(Above(Sum({<Year>} Value), 0, RowNo())), (Year, (NUMERIC))))

Re: only min value in pivot

beck_bakytbek — Tue, 14 Mar 2017 10:38:02 GMT

Sunny Thanks a lot for your help and taking the time to help

Re: only min value in pivot

sunny_talwar — Tue, 14 Mar 2017 10:40:01 GMT

No problem at all