Month by Month report

[#1]

Hi,

would like to create a fixed report with jan to dec month and then ytd values in the end of the report

I have problem to create a set syntax with fixed columns, so that we always start with jan and if I select for
example sep I would have values from Jan to Sep and then 0 in oct to dec.

I have used the follwoing syntax

sum({$<Year = {$(=only(Year)) }, Month = {"=$(>=(Month)-11)"}>}[Invoiced sales amount])

But the problem is that wonth is not fixed .

Regards mikael

[#2]

add another clause at the end to ignore any Month selection by writing Month=

so in your formula...

sum({$<Year = {$(=only(Year)) }, Month = {"=$(>=(Month)-11)"}, Month=>}[Invoiced sales amount])

[#3]

Hi and thanks for the quick response...

I would like to use the month selection. For example If i select september as month I would like

to have values in my columns from jan, feb ... to sep and 0 in okt to dec.

and if i select februari I would only have data in two columns of 12.

is it possible ?

regards mikael

[swuehl]

Hi Mikael,

you could try

=sum({$<Year = {$(=only(Year)) }, Month = {"<=$(=max(Month))"}>}[Invoiced sales amount])

and you may have to clear additional fields that may interfere, like Date field (if you have one)

=sum({$<Year = {$(=only(Year)) }, Month = {"<=$(=max(Month))"}, Date= >}[Invoiced sales amount])

For point in time reporting using set analysis, I found this blog series quite interesting:

http://iqlik.wordpress.com/2010/11/27/the-magic-of-set-analysis-point-in-time-reporting/

Hope this helps,

Stefan