Show figures for last day of month

[hopkinsc]

Hi all,

I have the following fields..

ClosingStock

Date

MonthYear

Store

i have a pivot table with dimensions of:

Store and MonthYear (Pivoted along the top)

and an expression of Sum(ClosingStock)

The above expression sums the closing stock for each month, but i only want to to give me the closing stock figure for the last day of the month, not everyday of the month..

I have attached a sample. any help would be appreciated.

Thanks

[rustyfishbones]

Have tried adding this to the script

MonthEnd(Date) as EndofMonth

[hopkinsc]

Hi Alan,

I have added that but how to i get the figure needed in my chart?

[#4]

Try this expression

=FirstSortedValue(ClosingStock,-Date)

[#5]

Check the attachment

[tresesco]

May be like attached sample:

First Dim: Store

Second Dim(Calculated): Month(Date)

Expression: FirstSortedValue( ClosingStock, -Date)

[MK_QSL · Accepted solution]

Use script like below..

Data:

Load

  *,

  IF(Date = MonthEndDate,1,0) as Flag;

LOAD Store,

     Date,

     FLOOR(MonthEnd(Date)) as MonthEndDate,

     MonthYear,

     ClosingStock

FROM

Sample.xlsx

(ooxml, embedded labels, table is Sheet1);

=========================

Now create a pivot table

Dimensions = Date and Store

Expressions = SUM({<Flag = {1}>}ClosingStock)

[its_anandrjs]

Load your table for max date of the month that is month end try the below code for this

Data:

LOAD Store,

     Date,

     MonthYear,

     ClosingStock,

     MonthEnd(Date) as EndofMonth

FROM

(ooxml, embedded labels, table is Sheet1);

Final:

LOAD

MonthYear,

MaxString(Date) as MonthEndDate

Resident Data

Group By MonthYear;

And in pivot table

Dimension1:- Store

Dimension2:- MonthEndDate

Expression:- Sum(ClosingStock)

Regards