3 Replies Latest reply: Oct 15, 2011 5:38 PM by Stefan Wühl

# Month by Month report

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

• ###### Month by Month report

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])

• ###### Month by Month report

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

• ###### Month by Month report

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