Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I have a table of activities in Qlik Sense displayed by month.
I just want the expression to be calculated by month ONLY when the month is selected. If not, I don't want to show this column in the table.
For example: if the user selects JANUARY, I Just want to show this month, not the others.
I think it is not possible with Sense because I can only put a condition in the whole object (table) not in each expression.
Thank you in advance.
I see. First, you might create a variable called vMes, then give it the definition: =GetFieldSelections([Mes])
If your chart, it should have only two columns. The field for the measures would then be: ='$(vMes)'
It gets a little trickier if you are trying to do a formula on the month. Also, one issue with this is that you can only select one month at a time or else the chart will not show correctly. Therefore, I use a listbox extension that allows me to limit the selections to 1 for a given field: http://bi-era.com/qlik-sense-listbox-extension/
Hi Anna,
Use this in your measures and it should work:
Example: (Replace where ever required)
sum( {$ < Month = {'$(=GetFieldSelections(Month, chr(39) & ',' & chr(39)))'} >}[Sales])
Thanks,
Sangram.
I have tested it!
Nice, it allows multiple selections in the month field.
Yes Kim, it does!
Hello Sangram,
Thank you so much.. I have tried with your expression but it does not disappered when I unselect the option...
I used this and the result of the expression is correct:
sum({$<meses={'$(=GetFieldSelections(meses, chr(39) & ',' & chr(39)))'} >}Enero)
any suggestion?
thanks a lot in advance
I don't want to show Enero in this case but it still appears:
Haven't test, but have done similar...
Try loading the data into rows, like:
tabla1:
CrossTable([Month],CountOfJob,2)
LOAD [Cod. Cambio],
[Descripción],
[Trabajador],
If([Total Imputado]='',NULL(),[Total Imputado]) as [Total Imputado],
If(Enero='' or Enero=0,NULL(),Enero) as Enero,
If(Febrero='',NULL(),Febrero) as Febrero,
If(Marzo='',NULL(),Marzo) as Marzo,
If(Abril='' or Abril=0 or len(Abril)=0,NULL(),Abril) as Abril,
If(Mayo='',NULL(),Mayo) as Mayo,
If(Junio='',NULL(),Junio) as Junio,
If(Julio='',NULL(),Julio) as Julio,
If(Agosto='',NULL(),Agosto) as Agosto,
If(Septiembre='',NULL(),Septiembre) as Septiembre,
If(Octubre='',NULL(),Octubre) as Octubre,
If(Noviembre='',NULL(),Noviembre) as Noviembre,
If(Diciembre='',NULL(),Diciembre) as Diciembre
FROM [lib://Desktop/Book3.xlsx]
(ooxml, embedded labels, table is Sheet1, filters(
Remove(Row, RowCnd(Compound,
RowCnd(CellValue, 1, StrCnd(null)),
RowCnd(CellValue, 2, StrCnd(null)),
RowCnd(CellValue, 3, StrCnd(null))
)),
Replace(1, top, StrCnd(null)),
Replace(2, top, StrCnd(null))
));
Then use Pivot table visualization and months should only show for selected data.
Hi Sangram , thanks for the code-
aggr(sum( {$ < dimension = {'$(=GetFieldSelections([dimension]'} >}measure)),GetFieldSelections(dimension])
I want to aggregate this on the selected dimension, i tired this but the hide functionality is overriding with this , so i thought i can try this, but this gives an invalid error- can you help here?
aggr(sum( {$ < dimension = {'$(=GetFieldSelections([dimension]'} >}measure)),dimension)