Using pivot table and master items in Set expressions

[Some1]

Hi all
I am trying to combine pivot table, master items and set expressions. What I am trying to achieve, is using the value of a column, which is a master item drill-down, in a set expression, in order to get a result that is specific to that column.
In my case, I have customers that might have used different services at any time, and my users will navigate through years, months and days using a drill-down. Let's say the possible services are called 1 and 200, and for any level of dates, I need to show:
- the number of distinct customers that have only used service 1
- the number of distinct customers that have only used service 200
- the number of distinct customers that have used both 1 and 200

For example: if I drill down to January 2024, I'll have one column per day. In the Jan 1, 2024 column, I want to count the customers that have used service 1 in that day, but not service 200 in that same day, regardless to the other days of the month.

Accordingly, if I drill up to 2024, I'll see one column per month, and in the January column I want to show the customers that have used 1 in January but not 200 in January, and so on.

So what I've done:
- I have created a master item, drill-down, combining Year, Month and Day, and used it as a column in the pivot table
- I have created measures with set expression, using the only() function to check if there is only one day, one month or one year selected, and creating a different set expression accordingly.

Below is an example of one of the measures, aiming to count the customers that used 1 but not 200 in a period of time. However, if I drill down to a month, it still counts the access to services in the whole month, not in any single day.

In order to check if I can correctly assess the drill down level in a formula, I have added a "Date Test" measures, that correctly shows only the year, year and month, or full date according to the level drilled. But still I can't sort my issue out.

In the attached example app I have added some KPIs showing the desired result on 2024, Jan 2024, Jan 15 2024

Any help will be highly appreciated!!!

= IF (
    IsNull(only([AccessDate.autoCalendar.Day])),
        IF (
            IsNull(only([AccessDate.autoCalendar.Month])),
                    IF (
                          IsNull(only([AccessDate.autoCalendar.Year])),
                          'Null',
                          Count({
                              <
                                CustomerID = (P({
                                    < ServiceID={"200"}, 
                                      AccessDate.autoCalendar.Year={"$(=Only(AccessDate.autoCalendar.Year))"}
                                    >
                                }) 
                                - 
                                P({
                                    < ServiceID={"1"}, 
                                      AccessDate.autoCalendar.Year={"$(=Only(AccessDate.autoCalendar.Year))"}
                                    >
                                }) )
                              > 
                            } DISTINCT CustomerID)
                      ),
            Count({
                <
                  CustomerID = (P({
                      < ServiceID={"200"}, 
                        AccessDate.autoCalendar.Year={"$(=Only(AccessDate.autoCalendar.Year))"},
                        AccessDate.autoCalendar.Month={"$(=If(GetSelectedCount(AccessDate.autoCalendar.Month) > 0, Only(AccessDate.autoCalendar.Month), '*'))"}
                      >
                  }) 
                  - 
                  P({
                      < ServiceID={"1"}, 
                        AccessDate.autoCalendar.Year={"$(=Only(AccessDate.autoCalendar.Year))"},
                        AccessDate.autoCalendar.Month={"$(=If(GetSelectedCount(AccessDate.autoCalendar.Month) > 0, Only(AccessDate.autoCalendar.Month), '*'))"}
                      >
                  }) )
                > 
              } DISTINCT CustomerID)
        )
          ,
    Count({
        <
          CustomerID = (P({
              < ServiceID={"200"}, 
                AccessDate.autoCalendar.Year={"$(=Only(AccessDate.autoCalendar.Year))"},
                AccessDate.autoCalendar.Month={"$(=If(GetSelectedCount(AccessDate.autoCalendar.Month) > 0, Only(AccessDate.autoCalendar.Month), '*'))"},
                AccessDate.autoCalendar.Day={"$(=If(GetSelectedCount(AccessDate.autoCalendar.Day) > 0, Only(AccessDate.autoCalendar.Day), '*'))"}
              >
          }) 
          - 
          P({
              < ServiceID={"1"}, 
                AccessDate.autoCalendar.Year={"$(=Only(AccessDate.autoCalendar.Year))"},
                AccessDate.autoCalendar.Month={"$(=If(GetSelectedCount(AccessDate.autoCalendar.Month) > 0, Only(AccessDate.autoCalendar.Month), '*'))"},
                AccessDate.autoCalendar.Day={"$(=If(GetSelectedCount(AccessDate.autoCalendar.Day) > 0, Only(AccessDate.autoCalendar.Day), '*'))"}
              >
          }) )
        > 
      } DISTINCT CustomerID
    )
)

[lennart_mo]

Hi @Some1,

unfortunately I am unable to import your file into my QlikSense environment, could you post these desired results separately?

---

In the attached example app I have added some KPIs showing the desired result on 2024, Jan 2024, Jan 15 2024

---

[Some1]

Hi thankls for your interest in my issue.
I'm attaching the script I have used to create the data, and the screenshot of the desired result if I drill down to 2024, Jan 2024, or Jan 15, 2024.

Basically I need to find a way to use the column or row name in the set expression, but I keep getting the data according to the active filters.

cheers

[lennart_mo]

Hi!

I've finally gotten around to trying a few different approaches and I think I might have found a solution for your problem. I used the following formula:

if(
	IsNull(GetFieldSelections(AccessDate.autoCalendar.Year)),

	Sum(
	AGGR(
	(Count({<ServiceID={1}>} DISTINCT CustomerID) - Count({<ServiceID={200}>} DISTINCT CustomerID))=1
	, CustomerID, AccessDate.autoCalendar.Year)*-1
	),
    
    if(
    
    	IsNull(GetFieldSelections(AccessDate.autoCalendar.Month)),
    
    	Sum(
		AGGR(
		(Count({<ServiceID={1}>} DISTINCT CustomerID) - Count({<ServiceID={200}>} DISTINCT CustomerID))=1
		, CustomerID, AccessDate.autoCalendar.Month, AccessDate.autoCalendar.Year)*-1
		),
    
    	Sum(
		AGGR(
		(Count({<ServiceID={1}>} DISTINCT CustomerID) - Count({<ServiceID={200}>} DISTINCT CustomerID))=1
		, CustomerID, AccessDate.autoCalendar.Day, AccessDate.autoCalendar.Month, AccessDate.autoCalendar.Year)*-1
		)
	)
    
)

Let me know if this gives you your desired results!

Cheers

[Some1]

Hi @lennart_mo , thanks. I'm trying to understand the logic but I'm not expert enough. I guess this shoud count the customers that used service 1 but not 200 in the period. I'm not sure why the result is then multiplied *-1.
To have the other combinations I guess I should do:

(Count({<ServiceID={200}>} DISTINCT CustomerID) - Count({<ServiceID={1}>} DISTINCT CustomerID))=1

to count the customers of 200 only. How should I count the customers of both services?

[lennart_mo · Accepted solution]

Hi @Some1,

I'll try to explain. The AGGR() Function gives us the ability to more or less create a table using dimensions different from our visualization. In this case, one table per each step of your drilldown.

Sum(
	AGGR(
	(Count({<ServiceID={1}>} DISTINCT CustomerID) - Count({<ServiceID={200}>} DISTINCT CustomerID))=1
	, CustomerID, AccessDate.autoCalendar.Year)*-1
)

So lets look at the first part of my expression:

In the AGGR() i use the dimensions CustomerID and Year, meaning I'll get one value per Customer and Year. This value is calculated by checking wether there were events with the according ServiceIDs.

If there was one during a year, the count should return 1. Since your initial request was to filter out those where the EventID was 1 but not 200, this formula

(Count({<ServiceID={1}>} DISTINCT CustomerID) - Count({<ServiceID={200}>} DISTINCT CustomerID))

returns 0 or -1 for any customer where an EventID = 200 occured.

To avoid having customers canceling each other out (i.e. one having only EventID 1, the other 200, resulting in 1 and -1) I added the "=1" part.

That leads us to the "*-1": This is necessary, since the comparison in our AGGR() only returns -1 and 0 (TRUE and FALSE), so not doing so would return a negative value.

To implement your new request I would consider changing the formula in the AGGR() as follows:

(Count({<ServiceID={1}>} DISTINCT CustomerID)*2 + Count({<ServiceID={200}>} DISTINCT CustomerID))

In doing so you'll get a distinct value for each of the four cases, so you only have to adjust the number the term should be equal to.

EventID = 1?	EventID = 200?	Return value
no	no	0
no	yes	1
yes	no	2
yes	yes	3

Hope this helps!

[Some1]

Hi @lennart_mo that works fine!!! I like the solution with the binary values of the two services, it can be applied also in case of combination of more services.
Thanks a lot! I've learnt a good trick.

cheers