Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
I am working on the straight table which has sum sale calculation..
I need to show current selection sale and sale in the same period 12 months ago.
My selection box Faktura/Returmeldt dato has format MMM-YYY
Ex
If I choose from nov 09 until jan 10, I have to be able to calculate and show also nov 08 - jan 09
Can anybody help?
I have tried with add months but then I get for the whole 12 month while I need for the chosen period, but 12 months ago
Regards
Wow, that text is hard to look at!
First, are your dates actual dates, but formatting like that or are they strings? I'll assume strings. First, you need the minimum selected date:
To get that same month for the previous year, you'll need some text manipulation:Min(Faktura)
The max is similar.Left(Min(Faktura), 4) & Right(Min(Faktura), 3)-1
Then use that for Set Analysis, something like this:
Sum({1<Faktura = {'>$(=Left(Min(Faktura), 4) & Right(Min(Faktura), 3)-1)
<$(=Left(Max(Faktura), 4) & Right(Max(Faktura), 3)-1)'}> Sales)
That's really messy, so I would suggest breaking it up into multiple variables and then referencing the variables to get something like:
Sum({1<Faktura = {'>$(vMin)<$(vMax)'}> Sales)
You may also need to mess with the quotes a bit to get everything into the proper format.