FirstSortedValue in chart object

[ruslanov]

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

[sunny_talwar]

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

[ruslanov]

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;

[sunny_talwar · Accepted solution]

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)

[ruslanov]

It works!

Thanks for the help!