Show the amount of the maximum date if you have not selected

[martin_caruso]

Hello good afternoon.

I have a table whose main fields are the following:

ACCOUNT	AMOUNT	YEAR	QUARTER
1	10	2017	Q1
1	15	2017	Q2
1	22	2017	Q3
1	50	2017	Q4
2	34	2018	Q1
2	45	2018	Q2
3	30	2017	Q1
3	5	2017	Q2
3	12	2017	Q3
3	28	2017	Q4

I need that when I have not selected the year or quarter, the indicator that I care about the maximum date (in this case, according to the data in the table, which should show the amount for the year 2018 and Q2). Then, it should show according to the selection you have.

Of course, I appreciate any kind of help you can give me.

Regards,

Martin.

[Thiago_Justen_]

May be this:

If(GetSelectedCount(Year)=0 and GetSelectedCount(Quarter)=0,Sum({<Year={$(=Max(Year))},Quarter={"$(='Q'&Max(Right(Quarter,1)))"}>}Value),Sum(Value))

I didn't test it but I guess it will be helpful.

[crusader_ · Accepted solution]

Hello Martin,

I'd suggest you to add one more column:

Num(YEAR & RIGHT(QUARTER, 1)) AS YEAR_QUARTER

Result: 20181

so you'll get unique numeric representation of combination year-quarter, that doesn't affect your RAM footprint even if you have a bazillion of rows (there no so many year quarter unique combinations), but significantly simplifies expression to:

Sum({<YEAR_QUARTER= {$(=MAX(YEAR_QUARTER))}>} AMOUNT)

NOTE: $(= ...) inside Set Analysis is performing calculations without context on full dataset, so it doesn't know that you're looking for specific year as well or any other additional conditions...

NOTE 2: Using numeric values in set analysis gives the best performance in calculation

NOTE 3: The expression below will always try to find Value for Q4 of max(year)...

Sum({<Year={$(=Max(Year))},Quarter={"$(='Q'&Max(Right(Quarter,1)))"}>}Value)

Hope this helps.

//Andrei

[keerthika]

Hi Martin,

You can try this also it's working fine for me if there is no selection, you can modify else condition (i.e, if there is any selection) based on your requirement.

=if(GetSelectedCount(YEAR)=0 and GetSelectedCount(QUARTER) =0,sum({<YEAR={$(=max(YEAR))},QUARTER={$(=chr(39)&MaxString({<YEAR={$(=max(YEAR))}>}QUARTER)&chr(39))}>}AMOUNT),0)

Regards,

Keerthika

[martin_caruso]

Thank you very much @Andrei Kaliahi!

Your answer was useful! Take this opportunity to consult: how do I do if I want to show the following without having a year or quarter selected?

CURRENT PERIOD	PREVIOUS PERIOD	PREVIOUS QUARTER
2018, Q1	2017, Q1	2017, Q4

The value for the current period I already have, I would be subtracted for the previous period and the previous quarter.

I appreciate your time and help.

Regards,

Martin.

[crusader_]

Hi Martin,

Assuming you used my approach listed above, then it's just a math

Current Year: 20181 , so you need to substract 10 to get previous year

Previous Year: 20181 - 10 = 20171

Sum({<YEAR_QUARTER= {$(=MAX(YEAR_QUARTER)-10)}>} AMOUNT)

Previous Quarter a bit more tricky: 20181 - if(mod(QY,10)=1,7,1)  = 20174 (if nothing selected)

So we substract 7 if this is first Q, otherwise just -1

Sum({<QY={$(=max(QY)-$(=if(mod(QY,10)=1,7,1)))}>} amount)

Hope this helps.

//Andrei

[martin_caruso]

Perfect Andrei Kaliahin, thank you very much!

Although I would be having a problem: the table should work as follows:

• If I do not have a year or quarter selected, you must show the last year and quarter. As we saw in the previous examples.

• But if I select, you must show me the month and the selected quarter.

What should I add to have both options in the table?

Thanks for your help.

Regards,

Martin.

[crusader_]

Hi Martin,

I'm not sure if I understood correctly what should show month? Same expression?

If you select year or quarter what month should be shown? first , second or third in quarter or random months of the year?

In general the idea stays pretty much the same just might be a bit more complicated logic.

Draw your solution on paper first, when you 100% sure what you're doing (covering all scenarios), just repeat the same in your algorithms.

Hope this helps.

//Andrei

[martin_caruso]

Thank you very much everyone for the help. Finally, I found an extension that allows me to set a default value selected in the selector. In this way, the indicators and graphics always show me the data based on the pre-established selection.

Regards,

Martin.