Formulae: RUNNINGTOTAL sorting issue?

cpvk · April 4, 2013 9:07AM

Hi

I have a question on the function RUNNINGTOTAL, and wondering if anyone could help.

I want to add Sales cash cumulatively by week for a particular SKU/Year combination.

I have the following formula in a field called "Cumulative Spend"

RUNNINGTOTAL([Sales Cash],SUBSET2([Sku],[Year]),false)

So this adds by year/SKU, but not in the right week order. I've tried to do a sort on the data-set to help. But was wondering if there was a smarter way. I have just downloaded the latest 2.8 version this morning.

Basically, it sort of works, but it doesn't add in Week order. Is there any way to make this happen?

Thanks

tjbate · April 4, 2013 9:24AM

Vishal - What is the data typing on the field [Year]... is it Text or Date/Time?
(or do you have one instance of both, which can be good practice).

If it is Text, do the Year values display in the Sidebar filter in the correct order? If not, you may need to tell Omniscope the correct sort order for the values in [Year], since it may think they are random alphanumeric Text, and not sequential Date/Time values...?

cpvk · April 4, 2013 9:42AM

Hi Thomas

The data typing for [Year] is Category as it is in the format 2012/13. It does seem to be in the correct order in the sidebar as well.

tjbate · April 4, 2013 10:27AM

Hmmm.. fiscal/corporate 2012/13 is not going to convert easily to a proper, continuous Date/Time format....

Could you post (or send to @Support) a sampled file with a few rows of data so we can look at what your options might be?

paola · April 10, 2013 12:29PM

When you add true/false as the last argument in the RUNNINGTOTAL function, the values will be added in your target field's [Sales Cash] sorting order. If you leave it blank, the running total will add values following the record order.
If your records are entered in chronological order, this will solve the problem. If there are any irregularities, you can use the Sort operation in the DataManager space, to create this order.

Welcome!

Categories

Ideas Parade

Tagged