Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
martin_caruso
New Contributor III

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

Hello good afternoon.

I have a table whose main fields are the following:

ACCOUNTAMOUNTYEARQUARTER
1102017Q1
1152017Q2
1222017Q3
1502017Q4
2342018Q1
2452018Q2
3302017Q1
352017Q2
3122017Q3
3282017Q4


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.

1 Solution

Accepted Solutions
crusader_
Valued Contributor

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

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

8 Replies
thiago_justen
Valued Contributor III

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

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.

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
crusader_
Valued Contributor

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

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
Contributor II

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

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
New Contributor III

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

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 PERIODPREVIOUS PERIODPREVIOUS QUARTER
2018, Q12017, Q12017, 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_
Valued Contributor

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

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
New Contributor III

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

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_
Valued Contributor

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

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
New Contributor III

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

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.

Community Browser