Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
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
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
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.
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
Perfect Andrei Kaliahin, thank you very much!
Although I would be having a problem: the table should work as follows:
What should I add to have both options in the table?
Thanks for your help.
Regards,
Martin.
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
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.