Handling of different time dimensions

[Anonymous]

Hello all,

I have, according to me, a tricky question about how to cope with two different time dimensions.

I have this data in my only table:

OrderId	OrderDate	OrderYear	OrderMonth	InvoiceDate	SalesAmount
A	2015-01-04	2015	Jan	2015-02-01	100.000
B	2015-11-15	2015	Nov	2016-01-31	50.000
C	2015-04-01	2015	Apr	2015-07-01	200.000

The total sales amount is 350.000, no selections.

I select 2015 as my OrderYear. The total sales amount is still 350.000, with OrderYear = 2015 as my only current selection.

Now to my point: As I have chosen 2015 as OrderYear I would like to create an expression that presents SalesAmountThatWillBeInvociedThisYear e.g. order A and C with the total value of 300.000.

Is that possible through an expression/set analysis? Or should I mess around with my time dimensions in my data model? Of course I have a hundred thousands of records and this is just a simple example.

Have I made clear what I am trying to receive?

Thanks for possible solutions.

Best,

Filip

[sunny_talwar]

Try something like this in the set analysis:

Sum({<[Invoice Date] = {"=Year([Invoice Date]) = 2015"}>}SalesAmount)

HTH

Best,

Sunny

[sunny_talwar]

Please find attached a sample.

Best,

Sunny

[Anonymous]

I would like a dynamic function. What if the user chooses Year 2014? Or June and July in 2013?
I would like to reflect the selection from OrderDate to InvoiceDate. When I select 2015 as OrderYear, there are 365 possible OrderDate values, and then I would like to make a set analysis code that only contains sales amount from the InvoiceDate values, that is actually choosen in my OrderDate field.

Do I make sense?

[sunny_talwar]

Have a look (PFA)

Best,

Sunny

[Anonymous]

Still not what I'm looking for...

[Anonymous]

Other solutions?

[sunny_talwar]

Do you want to try and explain it one more time then? What exactly you are looking to do?

Best,

Sunny

[Anonymous]

Don't know if I can explain better

Please read my original post again.

I want to create an expression that will show SalesAmountThatWillBeInvociedSelectedOrderPeriod, that needs to work no matter if i make selections in OrderDate, OrderMonth och OrderYear..

[sunny_talwar]

You want a single list box for dates, month and year which controls both order as well as invoice date, right? If this is true then I think what you need is a Master of a Master Calendar, something like what is presented here Canonical Date.