Help

[HAmzehAF]

Hello,
Can I get quick help with this?

In Qlik Sense, lets say i have a table:

• Store - Sum({<TYPE1={'1'}>} DISTINCT DOCNO)
  This gives me the sum of DOCNO for the Store.

• Division - Sum({<TYPE1={'2'}>} DISTINCT DOCNO)
  This gives me the sum of DOCNO for the Division.

• Category - Sum({<TYPE1={'3'}>} DISTINCT DOCNO)
  This gives me the sum of DOCNO for the Category.

I want to combine these using an IF statement, depending on the dimension I have.

Since I am using a drill-down in "bar chart", I want the IF statement to work with the drill-down as well.

[jody998]

Hello,

To achieve dynamic behavior in Qlik Sense that adapts to the drill-down dimension, you can use the GetCurrentField() function to determine the active dimension in the drill-down group. Then, you can use an IF statement to apply the correct logic based on the current dimension.

Here's how you can write the expression:

qlik
Sum(
DISTINCT
IF(
GetCurrentField([DrillDownGroup]) = 'Store',
IF(TYPE1 = '1', DOCNO),
IF(       Official Site
GetCurrentField([DrillDownGroup]) = 'Division',
IF(TYPE1 = '2', DOCNO),
IF(
GetCurrentField([DrillDownGroup]) = 'Category',
IF(TYPE1 = '3', DOCNO)
)
)
)
)
Explanation:
GetCurrentField([DrillDownGroup]):

This function returns the name of the current field in the drill-down group.
Replace [DrillDownGroup] with the name of your actual drill-down group.
Nested IF statements:

Depending on the dimension, we filter DOCNO using TYPE1.
For Store, we use TYPE1 = '1'.
For Division, we use TYPE1 = '2'.
For Category, we use TYPE1 = '3'.
Sum(DISTINCT ...):

Ensures that the aggregation works for distinct values of DOCNO based on the condition.
Example Drill-Down Group Setup:
If you have a drill-down group named SalesHierarchy containing Store, Division, and Category, replace [DrillDownGroup] with SalesHierarchy.

Alternative Using Set Analysis:
If performance becomes an issue, try achieving similar behavior using a master measure with conditions applied externally instead of relying heavily on IF. However, note that GetCurrentField() is key for drill-down functionality and works well in most cases.

Test this formula in your environment and adjust the names (DrillDownGroup, Store, Division, etc.) to match your actual field.
Best Regards
jody998

[diegozecchini]

Hi!
To achieve your goal in Qlik Sense, you can use the GetSelectedCount() or GetFieldSelections() function in combination with the IF statement to dynamically calculate the Sum() based on the dimension in your drill-down hierarchy.

Here’s a generic expression you can use in your bar chart:

Sum(
DISTINCT
IF(Match(GetCurrentField([DrillDownGroup]), 'Store'), DOCNO,
IF(Match(GetCurrentField([DrillDownGroup]), 'Division'), DOCNO * (TYPE1='2'),
IF(Match(GetCurrentField([div)...---decode focus whatever... ```
To combine these conditions dynamically in your drill-down hierarchy in Qlik Sense, you can use the GetCurrentField() function to detect the active dimension in your drill-down group and adjust the calculation accordingly. Here's how you can structure it:

Assume your drill-down group is named [DrillDownGroup] and contains Store, Division, and Category as dimensions. Use the following expression:

Sum(
DISTINCT
IF(GetCurrentField([DrillDownGroup]) = 'Store', DOCNO * (TYPE1='1'),
IF(GetCurrentField([DrillDownGroup]) = 'Division', DOCNO * (TYPE1='2'),
IF(GetCurrentField([DrillDownGroup]) = 'Category', DOCNO * (TYPE1='3'), 0)))
)

Use in a Bar Chart

Add this expression as the measure for your bar chart.
Ensure that [DrillDownGroup] is set as the dimension in your chart.

This dynamic measure will adapt to the active dimension in the drill-down and calculate the sum accordingly.

[Qrishna]

Whats the order of your Drill down?