Daily Average Sale from Sales MTD in crosstab, DMR.

[Kyne]

Hi All,

I'm trying to calculate daily average sale using sales MTD(already built in SSAS Cube) in crosstab, DMR model for current month by stores.

so the calculation would be [Sales MTD]/ [Day] (= 10, if today is 10th).

i've tried to create data item of "  extract(day,current_date) ".
but, it lasts forever when i've tried to run the report with that, even it was working fine in separated query only with the data item on the same report, tho.

also, i've tried using calculation based on [period] hierarchy by extracting day number from [Period].[Day].
but same result, just running, but never gets the result.

simply putting integer number in query calculation (i.e. [Sales MTD]/10 ) finishes in lest than 10 seconds,
and so does the report without daily average sales.

I couldn't use the dynamic parameter by javascript, because it has to be scheduled to deliver on a daily basis.

- Sales hierarchy is just simple.
sales         -> [Measures].[Sales]
sales MTD -> Sum(periodstodate([Period].[Financial Year].[Fin Month],[Period].[Financial Year].currentmember), [Measures].[Sales])

- Period hierarchy is
[Financial year].[Fin Qtr]
[Financial year].[Fin Month]
[Financial year].[Fin Day]

- to-be result

               Sales MTD Daily Avg.(if today is 10th)
Store A      $100         $10   ( 100/10)
Store B      $200         $20   ( 200/20)

any suggestions, or recommendations would be much appreciated.

Regards,

Kyne

[MFGF]

Hi All,

I'm trying to calculate daily average sale using sales MTD(already built in SSAS Cube) in crosstab, DMR model for current month by stores.

so the calculation would be [Sales MTD]/ [Day] (= 10, if today is 10th).

i've tried to create data item of "  extract(day,current_date) ".
but, it lasts forever when i've tried to run the report with that, even it was working fine in separated query only with the data item on the same report, tho.

also, i've tried using calculation based on [period] hierarchy by extracting day number from [Period].[Day].
but same result, just running, but never gets the result.

simply putting integer number in query calculation (i.e. [Sales MTD]/10 ) finishes in lest than 10 seconds,
and so does the report without daily average sales.

I couldn't use the dynamic parameter by javascript, because it has to be scheduled to deliver on a daily basis.

- Sales hierarchy is just simple.
sales         -> [Measures].[Sales]
sales MTD -> Sum(periodstodate([Period].[Financial Year].[Fin Month],[Period].[Financial Year].currentmember), [Measures].[Sales])

- Period hierarchy is
[Financial year].[Fin Qtr]
[Financial year].[Fin Month]
[Financial year].[Fin Day]

- to-be result

               Sales MTD Daily Avg.(if today is 10th)
Store A      $100         $10   ( 100/10)
Store B      $200         $20   ( 200/20)

any suggestions, or recommendations would be much appreciated.

Regards,

Kyne

Hi,

You could try the following approach:

[Sales MTD] / #timestampMask ($current_timestamp,'dd')#

I have a feeling this might be as quick as using a hard-coded integer

Cheers!

MF.