Count

[#1]

Hi All

can any one help me on the following scenario, I have 3 fields   Design Date, Sale Date Item , Sale, by using these three fields I would like to calculate Total Months count for each Item from Design date and cumulative sum of Sale for each item to Sale date.

Thanks

John

[PradeepReddy]

try bellow expression for cumulative sum.. (Pivot Table)

Dimension: Item,Sale Date

expr1: Sum(Sale)

expr2: if(isnull(above(sum(Sale),1))=1,sum(Sale),rangesum(sum(Sale),above(sum(Sale),1)))

[miguelbraga]

1. =Sum(MonthsDifference)

2. = aggr( NODISTINCT sum(Sale), Item ) OR max(Aggr(sum(Sale),Item)), depending on how you want to show the results.

Hope it helps

[#4]

No Luck

[#5]

Hi Miguel Braga, Could you please explain me how the

MonthDiff  variable will function here?

[miguelbraga]

MonthDiff will function based on how your start date ence [Design Date] is and your end date in your case [Sale Date]. I'm working on the count expression

[miguelbraga]

See this:

Calculating rolling n-period totals, averages or other aggregations

Or This:

How can I calculate a cumulative sum in load script?

For count

[sunny_talwar · Accepted solution]

Unable to get the results in months, but for days may be this:

Table:

LOAD [Design Date],

    [Sale Date],

    Item,

    Sale

FROM

[Testdata (3).xlsx]

(ooxml, embedded labels, table is Sheet1);

Concatenate(Table)

LOAD Only(Item) as Item,

  Date(Today()) as [Design Date],

    Date(Today()) as [Sale Date],

    0 as Sale

Resident Table

Group By Item;

Join(Table)

LOAD Min([Sale Date]) as [Min Sale Date],

  Item

Resident Table

Group By Item;

FinalTable:

LOAD *,

  [Sale Date] - [Min Sale Date] as DaysDifference,

  If(Item = Peek('Item'), RangeSum(Peek('Cumulative Mileage'), Sale), Sale) as [Cumulative Mileage]

Resident Table

Order By Item, [Sale Date];

DROP Table Table;

[#9]

Hi Sunny Thanks, in the Model final table

one more load with Load *,  Round(DaysDifference/30.4166666667) As DiffMonth ;

will give us Months.

Thanks again.

[sunny_talwar]

Awesome