Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
ksharpes
Creator
Creator

Only show fields when different from previous month

I wish to show a table, but only show data where one of the fields is a different value from the previous month.

 

Example

=IF([Coverage Group (All)]<>({<[Reporting Date]={'$(=monthend(MAX([Reporting Date]),-1))'},[Coverage Group (All)],[Coverage Group (All)],''))

 

So if the value in the field [Coverage Group (All)] is different to the value from the previous month then show it otherwise null.

 

The above however does not work.

3 Replies
JordyWegman
Partner
Partner

Hi,

A couple of thing:

  1. Use an aggregation for fields with values like: Sum([Coverage Group (All)])
  2. 'Single' quotes are seen as text. This is not going to work in your situation.
  3. "Double" quotes are seen as evaluations. This is what you need
=IF(Sum([Coverage Group (All)]) <> 
 Sum({<[Reporting Date]={"$(=monthend(MAX([Reporting Date]),-1))"} [Coverage Group (All)])
  ,Sum([Coverage Group (All)])
  ,'')

I can't test your formula, but try the above.

Jordy

Climber 

Work smarter, not harder
JordyWegman
Partner
Partner

Hi,

A couple of things:

  1. Use an aggregation for fields with values like: Sum([Coverage Group (All)])
  2. 'Single' quotes are seen as text. This is not going to work in your situation.
  3. "Double" quotes are seen as evaluations. This is what you need
=IF(Sum([Coverage Group (All)]) <> 
 Sum({<[Reporting Date]={"$(=monthend(MAX([Reporting Date]),-1))"} [Coverage Group (All)])
  ,Sum([Coverage Group (All)])
  ,'')

I can't test your formula, but try the above.

Jordy

Climber 

Work smarter, not harder
ksharpes
Creator
Creator
Author

[Coverage Group (All)] is a text field and no aggregation appears to work.

I have used single quotes using that set analysis previously and it works as expected on them

The below works:

=IF([Coverage Group (All)]<>[Coverage Group (Ext)],[Coverage Group (All)],'')

This is using a similar field.

Am I unable to use set analysis on if my field is text?