topic FirstSortedValue in chart object in QlikView

FirstSortedValue in chart object

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

Dear

We have table with some sales facts: 4 Salesman and fact of sales for 4 dates

There is a situation when the Salesman has no sales.

For example Salesman B has no sales at 01.02.2018

so there is need to show the last fact until that period (01.01.2018)

According to this logic I need do build a chart for each date we have a number of Salesman who has sales and who doesn't (if there is no sales, we take his last sale)

It works fine in a text object for each selected date

=sum({<Salesman,date>}if(aggr(FirstSortedValue(distinct{<date,date_indx={"<=$(vMax_date)"},Salesman>}Has_sale, -date),Salesman)='yes',1,0)) &' yes'

& chr(13) &

sum({<Salesman,date>}if(aggr(FirstSortedValue(distinct{<date,date_indx={"<=$(vMax_date)"},Salesman>}Has_sale, -date),Salesman)='no',1,0)) &' no'

but it doesn't work when i build a chart like this (for each date)

Needed result:

date	yes count	no count	Total
01.01.2018	2	2	4
01.02.2018	1	3	4
01.03.2018	3	1	4
01.04.2018	2	2	4

Pls, let me know if you have an idea how to solve this problem

Thanks

Ruslan

Re: FirstSortedValue in chart object

sunny_talwar — Fri, 03 Aug 2018 14:43:43 GMT

I think you might need to generate the missing data in order to accomplish this... are you okay making changes in the script?

Re: FirstSortedValue in chart object

ruslanov — Sat, 04 Aug 2018 04:47:26 GMT

Thanks for a advice.

Never used this approach before.

I tryed it, but have some problems.

it generates all dates instead of monthstartdate

Temp:

LOAD * INLINE [

Salesman, date,date_indx, Has_sale

A, 01.01.2018, 43101,yes

B, 01.01.2018, 43101,no

C, 01.01.2018, 43101,no

D, 01.01.2018, 43101,yes

A, 01.02.2018, 43132,yes

C, 01.02.2018, 43132,no

D, 01.02.2018, 43132,no

B, 01.03.2018, 43160,yes

C, 01.03.2018, 43160,yes

A, 01.04.2018, 43160,yes

B, 01.04.2018, 43191,no

D, 01.04.2018, 43191,no

];

Temp1:

LOAD Salesman,Has_sale,date,Date(If(Salesman=Peek(Salesman),Peek(date),date+1)) as date1

Resident Temp

Order By Salesman,date Desc;

NoConcatenate

LOAD !

Salesman,Has_sale,

If(date1 > date,Date(date+IterNo()-1)) as date

Resident Temp1

While date+IterNo()-1 < date1;

Drop Table Temp,Temp1;

Re: FirstSortedValue in chart object

sunny_talwar — Sun, 05 Aug 2018 23:07:22 GMT

Try this script

Table:

LOAD *,

Salesman&date_indx as Key;

LOAD * INLINE [

Salesman, date,date_indx, Has_sale

A, 01.01.2018, 43101,yes

B, 01.01.2018, 43101,no

C, 01.01.2018, 43101,no

D, 01.01.2018, 43101,yes

A, 01.02.2018, 43132,yes

C, 01.02.2018, 43132,no

D, 01.02.2018, 43132,no

B, 01.03.2018, 43160,yes

C, 01.03.2018, 43160,yes

A, 01.04.2018, 43191,yes

B, 01.04.2018, 43191,no

D, 01.04.2018, 43191,no

];

Temp:

LOAD DISTINCT Salesman

Resident Table;

Join (Temp)

LOAD DISTINCT date,

date_indx

Resident Table;

Concatenate (Table)

LOAD Salesman,

date,

date_indx,

'na' as Has_sale

Resident Temp

Where not Exists(Key, Salesman&date_indx);

DROP Table Temp;

DROP Field Key;

And then this

Dimension

date

Expression

Sum(Aggr(If(Has_sale = 'yes' or (Has_sale = 'na' and Above(Has_sale) = 'yes'), 1, 0) , Salesman, date))

Count(Salesman) - Sum(Aggr(If(Has_sale = 'yes' or (Has_sale = 'na' and Above(Has_sale) = 'yes'), 1, 0) , Salesman, date))

=Count(Salesman)

Re: FirstSortedValue in chart object

ruslanov — Tue, 07 Aug 2018 12:28:17 GMT

It works!

Thanks for the help!