topic Re: How to exclude the filter selection on the straight table w.r.t. Dimension in App Development

How to exclude the filter selection on the straight table w.r.t. Dimension

kakaderanjit53 — Tue, 26 Sep 2017 11:13:02 GMT

Hi Qlik Experts,

I have created a Dashboard in QlikSense,By using a straight table,in which displaying the categories and their totals wrt to amount and budget measures,but after selection of filters the total details(Categories) are not displaying,PFB details ,for more information,and kindly provide me the appropriate solution for how to restrict the dimensions after selection of filters:

1. Before filter Selection:

2. After Filter Selection:

In the 2nd screenshot after selection of filters the Totals of Categories are not appearing on the Qliksense dashboard.

please provide me solution for the same.

Thanks and regards,

Ranjit Kakade.

Message was edited by: RANJIT KAKADE

Re: How to exclude the filter selection on the straight table w.r.t. Dimension

sunny_talwar — Tue, 26 Sep 2017 11:30:38 GMT

What is your expression?

Re: How to exclude the filter selection on the straight table w.r.t. Dimension

kakaderanjit53 — Tue, 26 Sep 2017 11:33:31 GMT

Hi Sunny,

No Expression is here.

Re: How to exclude the filter selection on the straight table w.r.t. Dimension

sunny_talwar — Tue, 26 Sep 2017 11:36:26 GMT

How were you getting totals without expressions?

Re: How to exclude the filter selection on the straight table w.r.t. Dimension

kakaderanjit53 — Tue, 26 Sep 2017 11:40:26 GMT

for totals of Categories ,Expression is:

Need to make the dimension value as static irrespective of any selection.

Re: How to exclude the filter selection on the straight table w.r.t. Dimension

sunny_talwar — Tue, 26 Sep 2017 11:41:45 GMT

Expression is: ??? What is it?

Re: How to exclude the filter selection on the straight table w.r.t. Dimension

sunny_talwar — Tue, 26 Sep 2017 12:24:26 GMT

May be like this

if(WildMatch(Only({1} MagCategoryName),'Sales of Products - TOTAL')>0,

sum({1<MagCategoryID={'98','428'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Sales of Freight - TOTAL')>0,

sum({1<MagCategoryID={'99','421'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'GR6122-Sales of Other Services - TOTAL')>0,

sum({1<MagCategoryID={'435','436'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'FX Hedge impact on Sales - TOTAL')>0,

sum({1<MagCategoryID={'101','433'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Sales of Production - TOTAL')>0,

sum({1<MagCategoryID={'98','420','678','99','421','687','435','436','688','101'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Sales-Correction(TOTAL)')>0,

sum({1<MagCategoryID={'419','438'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Total Sales in COS')>0,

sum({1<MagCategoryID={'98','420','678','99','421','687','435','436','688','101','433','658','103','104','419','438','659'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'GR622 - Raw Materials - Total')>0,

sum({1<MagCategoryID={'430','439','689','440','442','690','443','444','660','445','446','691','447','448','692','449'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),' Raw Materials - Crude Ore Used - TOTAL')>0,

sum({1<MagCategoryID={'430','449'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),' Raw Materials - Purchased Ore Used - TOTAL')>0,

sum({1<MagCategoryID={'440','442'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),' Mineral reserves depletion and royalties-TOTAL')>0,

sum({1<MagCategoryID={'110','452'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'GR626-IG Trading Purchase - TOTAL')>0,

sum({1<MagCategoryID={'519','521'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),' Freight - Other - TOTAL')>0,

sum({1<MagCategoryID={'599','600'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),' Freight - Other - TOTAL')>0,

sum({1<MagCategoryID={'117','611'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Overburden&StrippingCosts(TOTAL)')>0,

sum({1<MagCategoryID={'121','614'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Detail V1(Total)')>0,

sum({1<MagCategoryID={'124','616'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Detail V2(TOTAL)')>0,

sum({1<MagCategoryID={'125','617'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Detail V2(TOTAL)')>0,

sum({1<MagCategoryID={'142','624'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Gas(TOTAL)')>0,

sum({1<MagCategoryID={'118','612'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Energy-Others(TOTAL)')>0,

sum({1<MagCategoryID={'119','613'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Energy - Coal(TOTAL)')>0,

sum({1<MagCategoryID={'184','615'}>} Total $(vNetBalance)),

sum({1<MagCategoryName={'*'}-{"*Std"}>}$(vNetBalance)))))))))))))))))))))))

Re: How to exclude the filter selection on the straight table w.r.t. Dimension

kakaderanjit53 — Tue, 26 Sep 2017 13:39:21 GMT

Hello Sunny,

my requirement is to not restrict the data w.r.t. dimension not related to measure i.e.

If I select any filter after that ,all MagCategory should be display in my dashboard,wherever I select any filter . but

right now If I select any filter only associated data is reflecting in dashboard.

Re: How to exclude the filter selection on the straight table w.r.t. Dimension

sunny_talwar — Tue, 26 Sep 2017 13:42:11 GMT

Like I mentioned, I am not sure why this happens... would you be able to share a sample with dummy data may be?

Re: How to exclude the filter selection on the straight table w.r.t. Dimension

kakaderanjit53 — Thu, 28 Sep 2017 14:42:25 GMT

Hi Sunny ,

I have resolved my issue by using following expression:

if(WildMatch(Only({1} MagCategoryName),'Sales of Products - TOTAL')>0,

sum({1<MagCategoryID={'98','428'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Sales of Freight - TOTAL')>0,

sum({1<MagCategoryID={'99','421'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'GR6122-Sales of Other Services - TOTAL')>0,

sum({1<MagCategoryID={'435','436'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'FX Hedge impact on Sales - TOTAL')>0,

sum({1<MagCategoryID={'101','433'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Sales of Production - TOTAL')>0,

sum({1<MagCategoryID={'98','420','678','99','421','687','435','436','688','101'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Sales-Correction(TOTAL)')>0,

sum({1<MagCategoryID={'419','438'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Total Sales in COS')>0,

sum({1<MagCategoryID={'98','420','678','99','421','687','435','436','688','101','433','658','103','104','419','438','659'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'GR622 - Raw Materials - Total')>0,

sum({1<MagCategoryID={'430','439','689','440','442','690','443','444','660','445','446','691','447','448','692','449'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),' Raw Materials - Crude Ore Used - TOTAL')>0,

sum({1<MagCategoryID={'430','449'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),' Raw Materials - Purchased Ore Used - TOTAL')>0,

sum({1<MagCategoryID={'440','442'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),' Mineral reserves depletion and royalties-TOTAL')>0,

sum({1<MagCategoryID={'110','452'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'GR626-IG Trading Purchase - TOTAL')>0,

sum({1<MagCategoryID={'519','521'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),' Freight - Other - TOTAL')>0,

sum({1<MagCategoryID={'599','600'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),' Freight - Other - TOTAL')>0,

sum({1<MagCategoryID={'117','611'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Overburden&StrippingCosts(TOTAL)')>0,

sum({1<MagCategoryID={'121','614'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Detail V1(Total)')>0,

sum({1<MagCategoryID={'124','616'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Detail V2(TOTAL)')>0,

sum({1<MagCategoryID={'125','617'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Detail V2(TOTAL)')>0,

sum({1<MagCategoryID={'142','624'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Gas(TOTAL)')>0,

sum({1<MagCategoryID={'118','612'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Energy-Others(TOTAL)')>0,

sum({1<MagCategoryID={'119','613'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} MagCategoryName),'Energy - Coal(TOTAL)')>0,

sum({1<MagCategoryID={'184','615'}>} Total $(vNetBalance)),

sum({1<MagCategoryName={'*'}-{"*Std"},[Business Unit Name]=,CompanyName=>}$(vNetBalance)))))))))))))))))))))))

Re: How to exclude the filter selection on the straight table w.r.t. Dimension

sunny_talwar — Thu, 28 Sep 2017 14:57:51 GMT

What is the difference to what I provided above?

Re: How to exclude the filter selection on the straight table w.r.t. Dimension

sunny_talwar — Thu, 28 Sep 2017 15:11:20 GMT

I used a comparison tool to compare the two expressions.... the only thing that was different between the two expression is the last line

Mine

sum({1<MagCategoryName={'*'}-{"*Std"}>}$(vNetBalance)))))))))))))))))))))))

vs. yours

sum({1<MagCategoryName={'*'}-{"*Std"},[Business Unit Name]=,CompanyName=>}$(vNetBalance)))))))))))))))))))))))

Do you really need to ignore selections in ,[Business Unit Name]=,CompanyName= even though you are using 1 to ignore all selections?