Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Thanks.
Would you be able to give few rows of sample and what you expect to see out of that sample?
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?
Unfortunately, I won't have time to think it through. But I really hope someone is able to offer help
Best,
Sunny
Thanks, Np.
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!!
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))
Anyone?