line chart issue

[#1]

Hi

i have year data i want to show line chart as

YTD Actuals+remaining months multiplyed by YTD Actuals Average

YTD Actuals=(10+20+30+40+50)/5= 30

Input:                                      

Jan    10                                     

Jan    10

Feb    20

Mar    30

Apr    40

May   50

jun     40

jul     50

aug   60

sep   90

oct  100

Nov  34

dec    45

Output Like this:

Jan   10

Feb    20

Mar    30

Apr    40

May   50

jun     30

jul    30

aug   30

sep   30

oct  30

Nov  30

dec    30

Please help thanks

[sunny_talwar]

You are looking to get this in the script or front end? Jan is repeating twice in your input table you provided, is this a typo or is this intentional?

[swuehl]

I assume your MonthField values show a numeric representation, i.e. they've been created e.g. using Month() QV function.

Then you should be able to create your chart with dimension MonthField and maybe an expression like

=If(MonthField <= Month(Today() ),

     Sum(Actuals),

     Sum(TOTAL {<MonthField = {"<=$(=Month(Today()))"}>} Actuals) / Month(Today() )

)

[#4]

Thanks its typo mistake Its Feb

If we can achevie I front end that's good otherwise back end also no issue

[sunny_talwar]

Have you checked out the expression provided by Stefan below? It is a front end option and you might want to check it out

[sunny_talwar]

I was not able to make Stefan's expression work even after a having a numeric representation for Month, but I used the same idea and used the date instead of Month.

Script:

Table:

LOAD Month(Date#(Month, 'MMM')) as Month,

  Value,

  Date(Date) as Date;

LOAD * Inline [

Month, Value, Date                                   

Jan, 10, 01/01/2016

Feb, 20, 02/01/2016

Mar, 30, 03/01/2016

Apr, 40, 04/01/2016

May, 50, 05/01/2016

Jun, 40, 06/01/2016

Jul, 50, 07/01/2016

Aug, 60, 08/01/2016

Sep, 90, 09/01/2016

Oct, 100, 10/01/2016

Nov, 34, 11/01/2016

Dec, 45, 12/01/2016

];

Create a chart with Month as dimension and the following Expression:

=If(Month <= Month(Today()), Sum(Value), Avg(TOTAL {<Date = {"$(='>=' & YearStart(Max(Date)) & '<=' & MonthEnd(Today()))"}>} Value))

I have created Date(Date) as Date in the script, so I did not have to use Date() function to fix my formatting in the set analysis expression here

Date = {"$(='>=' & YearStart(Max(Date)) & '<=' & MonthEnd(Today()))"}

But in case your date format is something else, you might need to fix the format like this:

Date = {"$(='>=' & Date(YearStart(Max(Date)), 'DateFieldFormatHere') & '<=' & Date(MonthEnd(Today()), 'DateFieldFormatHere'))"}

Dates in Set Analysis‌

[swuehl]

Seems like in this & my case, we need to use

=If(Month <= Month(Today()), Sum(Value), SUm(TOTAL {<Month = {"$(='<=' & Num(Month(today())))"}>} Value) /Month(today()))

Note that there is a difference between my and Sunny's expression if you are using data for more than 1 year, without having year selected. But if you are really interested in CY to date, you then need to also limit the first Sum(). I also would be careful to use Avg() here, in case you are using more than one record per Month. And in case you have more than 1 year of data and future dates also, but you are only interested in CY to date, use also today() in the lower limit of the advanced search:

=If(Month <= Month(Today()),

    Sum( {<Date = {"$(='>=' & YearStart(Today()) & '<=' & MonthEnd(Today()))"}>} Value),

    Sum(TOTAL {<Date = {"$(='>=' & YearStart(Today()) & '<=' & MonthEnd(Today()))"}>} Value) / Month(Today())

)

or

=If(Month <= Month(Today()),

    Sum({<Year = {$(=Year(today()))},Date=>}Value),

    Sum(TOTAL {<Month = {"$(='<=' & Num(Month(today())))"},Year = {$(=Year(today()))}, Date>} Value)/Month(today())

)

[Having created an additional Year field in the model]

You see, it's getting slightly into using more and more set analysis depending on your real setting and requirements.

[#8]

thanks ,

is there any way to implement in Back end script in front end i have lot of expressions

[sunny_talwar]

For backend, you can try like this:

Table:

LOAD Month(Date#(Month, 'MMM')) as Month,

  Value,

  Date(Date) as Date;

LOAD * Inline [

Month, Value, Date                                   

Jan, 10, 01/01/2016

Feb, 20, 02/01/2016

Mar, 30, 03/01/2016

Apr, 40, 04/01/2016

May, 50, 05/01/2016

Jun, 40, 06/01/2016

Jul, 50, 07/01/2016

Aug, 60, 08/01/2016

Sep, 90, 09/01/2016

Oct, 100, 10/01/2016

Nov, 34, 11/01/2016

Dec, 45, 12/01/2016

];

Left Join(Table)

LOAD Avg(Value) as Avg

Resident Table

Where Date <= MonthEnd(Today());

FinalTable:

NoConcatenate

LOAD Date,

  Month,

  If(Date <= MonthEnd(Today()), Value, Avg) as Value

Resident Table;

DROP Table Table;

[swuehl]

There are multiple ways to achieve what you want, the best way can only be determined when knowing more details about your data and requirement.

Here is an approach with a single table load:

Table:

LOAD *, If(Month(Today()) >= Month, Value, Acc / Month(Today())) as NewValue;

LOAD *, Rangesum(Peek('Acc'),If(Month(Today()) >= Month,Value)) as Acc;

LOAD Month(Date#(Month, 'MMM')) as Month,

  Value,

  Date(Date) as Date;

LOAD * Inline [

Month, Value, Date                                   

Jan, 10, 01/01/2016

Feb, 20, 02/01/2016

Mar, 30, 03/01/2016

Apr, 40, 04/01/2016

May, 50, 05/01/2016

Jun, 40, 06/01/2016

Jul, 50, 07/01/2016

Aug, 60, 08/01/2016

Sep, 90, 09/01/2016

Oct, 100, 10/01/2016

Nov, 34, 11/01/2016

Dec, 45, 12/01/2016

];