topic Re: Firstsortedvalue not working in QlikView

Firstsortedvalue not working

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

i would like to show startdate of sales for each country ..my date format is like below

fy2015,20152

my2015,20151

fy2014,20142

my2014,20141

just want to show start date as first sales date or first business started year.

can you help please?

Re: Firstsortedvalue not working

MayilVahanan — Fri, 05 Aug 2016 06:36:54 GMT

Try like this

country	Start Date
Chile	FY2014
China	MY2014
Switzerland	FY2013
UK	MY2013
US	FY2013

=FirstSortedValue(Period, PeriodActual)

in back end

Test:

Load * inline

[

Period, PeriodActual,country,sales

FY2015,20152,Switzerland,23000

FY2013,20132,Switzerland,13000

MY2015,20151,China,121000

FY2014,20142,Chile,221211

MY2014,20141,China,21300

FY2013,20132,US,321310

FY2015,20152,US,321313

MY2013,20131,UK,311421

];

LOAD country, FirstSortedValue(Period, PeriodActual) as StartDate Resident Test

Group by country;

country	StartDate
Chile	FY2014
China	MY2014
Switzerland	FY2013
UK	MY2013
US	FY2013

Re: Firstsortedvalue not working

gauthamchilled — Fri, 05 Aug 2016 06:43:51 GMT

Thanks mayil, but =FirstSortedValue(Period, PeriodActual) is not working , find the attachment and can you help to look please

Re: Firstsortedvalue not working

rajuamet — Fri, 05 Aug 2016 06:45:43 GMT

Hi Gautham Prasad,

Your requirement is not clear. Can you show us required output ?

Re: Firstsortedvalue not working

gauthamchilled — Fri, 05 Aug 2016 06:48:54 GMT

output is already available in the thread

Re: Firstsortedvalue not working

ramasaisaksoft — Fri, 05 Aug 2016 06:49:43 GMT

Hi Gautham,

1)As per your text object,you mentioned 2012 but there is no 2012 year

2)The data which you provide is not understandable for me

you want to show like

if business starts in US at 2012 you want 2012 sales only even though you selected any year in Filter am i right?

like that

even though you select 2014 as year for country Switzerland (this country starts the business 2 times in 2013 and 2015 so which one we need to show in data?)

sorry i am unable to understand your question tha's way i am asking these many questions

i hope you will get start year data for country wise ,you need to write set analysis/If conditions for each and every country.

Re: Firstsortedvalue not working

gauthamchilled — Fri, 05 Aug 2016 06:52:53 GMT

Mayils firstsortedvalue function is working but not working if i select any period from the filter..i think i need to show date irrespective of current selected year

Re: Firstsortedvalue not working

MayilVahanan — Fri, 05 Aug 2016 07:07:21 GMT

try like this

=FirstSortedValue({<Period=>}Period, PeriodActual)

Or go for Back end process.

Re: Firstsortedvalue not working

ramasaisaksoft — Fri, 05 Aug 2016 07:12:56 GMT

Hi Gautham,,

take a Staraight table objetct,

dim: Country

Expr :FirstSortedValue(Period,PeriodActual)

then you will get your solution

else

in code/script level you can fallow mayilvahanan solution

Re: Firstsortedvalue not working

gauthamchilled — Fri, 05 Aug 2016 08:07:13 GMT

Thanks mayilvahanan‌

I did the same, but it show 0 values for all the countries sales even though they dont have sales. start date is working fine.

Is there any way to get rid of those zeros..attached the file

Re: Firstsortedvalue not working

boopeshj — Fri, 05 Aug 2016 09:18:48 GMT

Hi Gautham Prasad

Is this what you were expecting ?

-Boo

Please mark correct answer if you find them.

Re: Firstsortedvalue not working

sunny_talwar — Fri, 05 Aug 2016 09:50:11 GMT

Are you looking for this output?

Changed the start date expression to this:

=If(Sum(sales) > 0, FirstSortedValue({<Period=>}Period, PeriodActual))