Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

siddharthsoam
Contributor

Expression not working in pivot table

Hi,

I am using below mentioned expression as a row in pivot table-

if(GetSelectedCount(H4)=0,restricted_customers,
if(GetFieldSelections(H4)='Customer Name',if(compare_customers='y',[Customer Name]),
if(GetFieldSelections(H4)='Customer Group',restricted_customers,restricted_customers

)))

The expression is working fine but when my H4 is not selected it is returning twice the sum of all the metrics in Totals as well as individual, I want that measures corresponding to restricted customers shall be shown when there is no selection. On the other hand the above expression is working fine in a regular table.

8 Replies
YoussefBelloum
Esteemed Contributor

Re: Expression not working in pivot table

Hi,

would you be able to show the issue and the exptected output via an image for example ? or attach a sample app or data ?

Luminary
Luminary

Re: Expression not working in pivot table

Hi:

I think that you can simplify the expression:

if(GetFieldSelections(H4)='Customer Name' and compare_customers='y', [Customer Name]), restricted_customers))

Best Regards,

Juan P. Barroso

siddharthsoam
Contributor

Re: Expression not working in pivot table

Hi Youssef,

Sorry not able to share qvf. But can you help we restrict the number of filters selections I get when I click on any filter content using this expression

sasiparupudi1
Honored Contributor III

Re: Expression not working in pivot table

Is this working  or are you battling still? Pl post sample output showing where it is doubling up

siddharthsoam
Contributor

Re: Expression not working in pivot table

Hi All,

Thanks for the help... I was able to solve the double showing up in my pivot when there is no filter selection by aggregating my measures with respect to restricted_customers. But I am still facing the issue of multiple filter selections when I select a particular customer group or customer name. I have attached a screenshot above as well.

MVP
MVP

Re: Expression not working in pivot table

Are either customer group or customer name expressions (or calculated dimension)? Selecting a calculated dimension causes selections on the underlying fields. The only workaround to prevent these selections is to add the calculated dimension to the load script.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
siddharthsoam
Contributor

Re: Expression not working in pivot table

Hi Jonathan,

How I am using getfieldselection() in the calculated dimension, how can I make this at backend..

MVP
MVP

Re: Expression not working in pivot table

You have a choice, either accept the extra selections  or make some changes to the load script. You are correct that GetFieldSelections can't be used in the load script so you will have redesign your data model slightly so that you can get that dimension as a field.

One other possibility that comes to mind. If you hide the fields with Set HidePrefix or Set HideSuffix (load script operations), then the fields will never show on the current selections. But 'never' means what is says and the fields will have to be renamed to contain the specified prefix or suffix.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser