Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
narayanamsn
Creator
Creator

How to Refer Value from a field without selecting it?

Hi Gurus,

I have a scenario in Pivot. Company Field has 2 values (a,b). we have 2 metrics (Sales/Expenses, Expenses/Sales)

Now I want to show 2 metrics only for company a. For company b want to show only one metric (Sales/Expenses)

If I enable conditional box in the expressions tab, it will work only when I select company a.

But I want to get the complete view without selecting any company like below snapshot..

   

Company Q1'17Q2'17Q3'17Q4'17Q1'18Q2'18Q3'18Q4'18Q1 y/y
Diff
Q2 y/y
Diff
Q3 y/y
Diff
Q4 y/y
Diff
Q1 y/y
%Change
Q2 y/y
%Change
Q3 y/y
%Change
Q4 y/y
%Change
aSales/Expenses4.655.846.955.7511.046.5313.811.826.400.706.87-3.921.380.120.99-0.68
aExpenses/Sales0.220.170.140.170.090.150.070.55-0.12-0.02-0.070.37-0.58-0.11-0.502.15
bSales/Expenses5.679.497.2111.798.474.992.2211.432.80-4.50-4.99-0.360.49-0.47-0.69-0.03

Pls find attached app.

Credits: Sunny had customized the view with all possible functional applications.

Kudos to Sunny

stalwar1

1 Solution

Accepted Solutions
sunny_talwar

6 Replies
sunny_talwar

Here you go

Capture.PNG

narayanamsn
Creator
Creator
Author

Small Tweek,

Have Data for 9 Qtrs (4 qtrs of 2 Yrs and 1 Qtr for 3rd year), In this case, comparison is missing others Qtrs (Q2,Q3,Q4) as the Data for these Qtrs is unavailable for the Max Year. Find below example.

we do have data for all 4 Qtrs of FY17, FY18 and have Q1'19 Data as well. Hence the table is not picking Q2,Q3,Q4 for comparison as the current expression is based on Max Year. How to qualify Max year with Qtr so that the issue can be fixed.

Q1'17Q2'17Q3'17Q4'17Q1'18Q2'18Q3'18Q4'18Q1'19Q1 y/y
Diff
Q2 y/y
Diff
Q3 y/y
Diff
Q4 y/y
Diff
Q1 y/y
%Change
Sales/Expenses5.679.497.2111.798.474.992.2211.431.31-7.15-4.99-2.22-11.43-0.84
Expenses/Sales0.180.110.140.080.120.200.450.090.760.64-0.20-0.45-0.095.45

stalwar1

sunny_talwar

I am not sure I follow?

narayanamsn
Creator
Creator
Author

in the attachment "% Change" Dimension -  the Table doesn't have the metrics updated for Q2,Q3 and Q4 %Change

Is the Reason being data is unavailable for Max Year (2019 for Q2,Q3,Q4)? !Capture.PNG

what changes to be done in expression?

sunny_talwar

Yes, that appears to be the problem... I think you need to change your set analysis to fix this... your set analysis is only looking at the FYear and not Month or Quarter....

narayanamsn
Creator
Creator
Author

Guru,

I got your answer, but haven't succeed yet to accommodate Quarter in the set analysis for Max Year condition ( FYear = {$(=Max(****FYear)-1)}>} ) , I am trying to fill **** in the expression with Quarter condition but couldn't succeed.