Cash collected in the current month.

[#1]

Hello All,

The above table shows a sales ledger extract.

I'm trying to calculate, by "cust ref", the current month "total value" where "doc type" is equal to "SLCSH". This should give me the total amount of cash collected in the current month.

The output should look something like this.

Many thanks in advance.

Paul.

[Anil_Babu_Samineni]

Try like this in Straight Table

Dimension is -- Cust ref

Expression is

FirstSortedValue([Total value], Cust ref)

OR

FirstSortedValue([Total value], -Cust ref)

[#3]

Hi!

Try with this in your table:

1. select Cust ref as dimension

2. Expression: sum({$<[Doc type]={'SLCSH'}>} [Total value])

You just filtered by period

Regards!

[#4]

Hi,

This works but i really need it to only ever show the current month.

Kind regards,

Paul.

[#5]

In Script I have declared vCurrMonth variable

Let vCurrMonth = Month(today());

To include current month in expression (other wise use @hugo morales expression above)

  =Sum(if(Month([Invoice date])= '$( vCurrMonth)' and [Doc Type]='SLCSH', [Total Value]))

]))

Set analysis expression worked with current month after I have loaded month column from csv file.

=Sum({<[Doc number]={'BACS'}, [Invoice Month] = {'$( vCurrMonth)'} >}[Total Value])

Load script

Sales:
LOAD [Cust ref],
      date([Invoice date]) as [Invoice date],
      month(date([Invoice date])) as [Invoice Month],//added extra month column
      [Doc number],
      [Doc Type],
      [Total Value]
FROM

(ooxml, embedded labels, table is Sheet1);

Note: Unless you have November month (current month) data, using current month in expression will result zero values.