Conditional calculation Qlik Sense

[#1]

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.

[TKendrick20]

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/

Qlik Sense Field Selection as Variable

[reddy-s]

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.

[reddy-s]

I have tested it!

[TKendrick20]

Nice, it allows multiple selections in the month field.

[reddy-s]

Yes Kim, it does!

[#7]

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

[#8]

I don't want to show Enero in this case but it still appears:

[dwforest]

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.

[prananellutla]

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)