Announcements
cancel
Showing results for
Did you mean:
Contributor

FirstSortedValue in chart object

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

1 Solution

Accepted Solutions
MVP

Try this script

Table:

Salesman&date_indx as Key;

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:

Resident Table;

Join (Temp)

date_indx

Resident Table;

Concatenate (Table)

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)

4 Replies
MVP

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

Contributor
Author

Never used this approach before.

I tryed it, but have some problems.

it generates all dates instead of monthstartdate

Temp:

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:

Resident Temp

Order By Salesman,date Desc;

NoConcatenate

Salesman,Has_sale,

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

Resident Temp1

While date+IterNo()-1 < date1;

Drop Table Temp,Temp1;

MVP

Try this script

Table:

Salesman&date_indx as Key;

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:

Resident Table;

Join (Temp)

date_indx

Resident Table;

Concatenate (Table)

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)

Contributor
Author

It works!

Thanks for the help!

Community Browser