Current year, last quarter set analysis

[MK9885]

Hello All,

I've a requirement to show data for

Current year, previous month and last quarter month

2015, Nov, Dec

The expression I've is

count({<Field1={1},Field2={1},[Year List], Quarter={'Q4'}, Month={'Dec'}>}DISTINCT(Field3))+

+sum({<Field1={1},Field4={1}>}(Field5))

The above expression is giving me for Year wise for last quarter and only for month of dec (to my understanding)

I also want to add another logic to his expression which should show me

Current Year, Previous Month and last month from Quarter.

And in 2017 it should be Current year, Current month (if Jan) and Jan month from Q1

2017, Jan, Jan

But if it is 2017 March, then

Current Year, Previous Month and Last month from Current Quarter

2017, Feb, Mar

I hope this is clear, please let me know if any confusion.

stalwar1‌

Thanks.

[sunny_talwar]

Would you be able to give few rows of sample and what you expect to see out of that sample?

[MK9885]

Sorry the Sample cannot be provided as you know the reasons and also I cannot think of any way to create the mock data to upload here.

Can you just help it without that?

[sunny_talwar]

Unfortunately, I won't have time to think it through. But I really hope someone is able to offer help

Best,

Sunny

[MK9885]

Thanks, Np.

[MK9885]

Hi Sunny stalwar1,

I've got the correct expression needed... following is the expression.

If (GetSelectedCount (Month) > 0 or GetSelectedCount (Quarter) > 0,

  count ({<Field1 ={1},  Field2={1}>} DISTINCT(Field2)),

  If (GetSelectedCount ([Year List]) > 0,

  If (GetFieldSelections ([Year List]) = year(today()) and GetSelectedCount (Month) = 0 and GetSelectedCount (Quarter) = 0,

  count ({<Field1 ={1} , [Month Number] = {01},Field2 = {1}>} DISTINCT(Field3)),

  If (GetSelectedCount (Month) = 0 and GetSelectedCount (Quarter) = 0,

  count ({<Field1={1},  [Month Number] = {03,06,09,12},Field2 = {1}>} DISTINCT(Field3)),

  )),

If (GetSelectedCount ([Year List]) = 0,

  count ({<Field1 ={1},  [Month Number] = {01},Field2= {1}>} DISTINCT((Field3)),

  If (GetSelectedCount (Month) = 0 and GetSelectedCount (Quarter) = 0,

  count ({<Field1 ={1},  [Month Number] = {$(=Max([Month Number]))},Field2= {1}>} DISTINCT(Field3))

  ,

//--

  count ({<Field1 ={1}, [Month Number] = {$(=Max([Month Number]))}, Field2 = {1}>} DISTINCT(Field3))))))

+sum({<Field1={1},Field4={1}>}(Field5))

Thanks for the help though!!

[MK9885]

Hello stalwar1‌

Though I've got the expression correct but there is a minor problem.

When I imply this formula, it is showing me the values for month of 11 but instead I want to show default as month 12.

To me the expressions looks correct and also the MaxMonth string seem correct as well...

Can you say where I'm doing it wrong?

Instead of counting 11th month I need 12th month.

Update:

I've managed to show the 12th month as default count in chart but I'm missing current month (2016)... can anyone help?

Thanks.

If (GetSelectedCount (Month) > 0 or GetSelectedCount (Quarter) > 0,

  count ({<Field1 ={1},  Field2={1}>} DISTINCT(Field2)),

  If (GetSelectedCount ([Year List]) > 0,

  If (GetFieldSelections ([Year List]) = year(today()) and GetSelectedCount (Month) = 0 and GetSelectedCount (Quarter) = 0,

  count ({<Field1 ={1} , [Month Number] = {01},Field2 = {1}>} DISTINCT(Field3)),

  If (GetSelectedCount (Month) = 0 and GetSelectedCount (Quarter) = 0,

  count ({<Field1={1},  [Month Number] = {03,06,09,12},Field2 = {1}>} DISTINCT(Field3)),

  )),

If (GetSelectedCount ([Year List]) = 0,

  count ({<Field1 ={1},  [Month Number] = {01},Field2= {1}>} DISTINCT((Field3)),

  If (GetSelectedCount (Month) = 0 and GetSelectedCount (Quarter) = 0,

  count ({<Field1 ={1},  [Month Number] = {$(=Max([Month Number]))},Field2= {1}>} DISTINCT(Field3))

  ,

//--

  count ({<Field1 ={1}, [Month Number] = {$(=Max([Month Number]))}, Field2 = {1}>} DISTINCT(Field3))))))

+sum({<Field1={1},[Month Number] = {$(=Max([Month Number]))},Field4={1}>}(Field5))

[MK9885]

Anyone?