Using subtotal within 1 dimensions

[EMJAE]

dear all,

I'm new in this community but I have the opportunity to create our finance reporting in Qlik;-).

For a table, I need to create "subtotals" within 1 dimension:

Eg Gross Sales - Deductions = Net Sales

Thanks.

[Mike_Dickson]

Hello @EMJAE 

Welcome to the Qlik community! In Qlik Sense, you can use expressions to calculate subtotals within a table. Here's how you can achieve the example you provided (Gross Sales - Deductions = Net Sales) using a Straight Table:

1. Add Dimension: First, add the dimension for which you want to calculate the subtotals. This could be a time period, a category, or any other field.

2. Add Gross Sales as a Measure: Next, add Gross Sales as a measure. You would do this by creating an expression that sums the Gross Sales for the dimension. For example:

   Sum([Gross Sales])

3. Add Deductions as a Measure: Similarly, add Deductions as a measure. For example:

   Sum([Deductions])

4. Calculate Net Sales as a Measure: Finally, create a new measure for Net Sales by subtracting Deductions from Gross Sales. You can do this by creating an expression that combines the previous two measures. For example:

   Sum([Gross Sales]) - Sum([Deductions])

5. Formatting and Subtotals: Optionally, you can format the numbers to display currency symbols, and you can enable subtotals for your dimension by going into the properties of the table, selecting the dimension, and turning on "Show Total".

6. Labeling the Measures: You may also want to label the measures appropriately by giving them names such as "Gross Sales", "Deductions", and "Net Sales".

Here's how it would look in Qlik Sense:

1. Add a Straight Table to your sheet.
2. Add your dimension (e.g., Month, Category, etc.).
3. Add three measures with the following expressions:
   • Gross Sales: Sum([Gross Sales])
   • Deductions: Sum([Deductions])
   • Net Sales: Sum([Gross Sales]) - Sum([Deductions])
4. Label your measures and format them as needed.
5. Enable subtotals for your dimension if needed.

This should give you a table with Gross Sales, Deductions, and Net Sales as columns, and subtotals within the dimension you have chosen.

[EMJAE]

Dear,

Thanks for the support, but the problem is that "Gross Sales" and "Deductions" are elements in 1 dimension "values".

So I should create a subtotal in 1 dimension with 2 elements.

Best regards,

Emilie

[Mike_Dickson]

If "Gross Sales" and "Deductions" are elements within a single dimension, then you'll need to use an expression to calculate the "Net Sales" as an additional element within the same dimension. This can be achieved using an Aggr function along with set analysis or conditional expressions.

Here is how you can achieve this in Qlik Sense:

1. Add Dimension: In your table, add the dimension which contains "Gross Sales" and "Deductions" (e.g., "Values").

2. Add Measure for Net Sales: Add a measure with an expression that calculates the Net Sales by subtracting Deductions from Gross Sales within the same dimension. You can use the Aggr function along with conditional expressions to achieve this. Here's an example expression that you might use (assuming your dimension is named "Values" and the measure that contains the actual numbers is named "Amount"):

   Sum(Aggr(If(Values = 'Gross Sales', Amount, 0), Values)) - Sum(Aggr(If(Values = 'Deductions', Amount, 0), Values))

   This expression calculates the sum of "Gross Sales" and subtracts the sum of "Deductions" within the "Values" dimension.

3. Label the Measure: Name your measure something appropriate like "Net Sales".

4. Customize the Table: Optionally, customize the table as needed, such as by formatting numbers or adjusting column widths.

This setup should allow you to have a table where "Gross Sales" and "Deductions" are elements within a single dimension, and "Net Sales" is calculated as a measure based on those elements.