# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Contributor II

## Data Intersection Question

Hi

I'm creating an analysis as follows:

 CustomerID Salesid Date C000001 A 31/01/2015 C000002 B 31/01/2015 C000003 C 31/01/2015 C000004 D 31/01/2015 C000001 E 28/02/2015 C000002 F 28/02/2015 C000005 G 28/02/2015 C000006 H 28/02/2015 C000005 I 31/03/2015 C000006 J 31/03/2015 C000007 K 31/03/2015 C000008 L 31/03/2015

When I select the month has to return to me the number of customers that bought the current month (selected) AND the previous month

Many thank's guys

Eduard

1 Solution

Accepted Solutions
Highlighted
MVP

## Re: Data Intersection Question

1) Define your VMonth and VPriorMonth variables with a leading equal sign:

=CalendarMonthNumber-1

2) Since you selected in CalendarMonthName, you need to clear this field in the p() function to avoid an incompatible set:

count({<CalendarYear={'\$(VYear)'},CalendarMonthNumber={'\$(VMonth)'},CustomerID=P({<CalendarYear={'\$(VYear)'},CalendarMonthNumber={'\$(VPriorMonth)'}, CalendarMonthName= >}CustomerID)>} distinct CustomerID)

You can also remove the first to set modifier, since they won't change anything compared to your default set:

count({<CustomerID=P({<CalendarYear={'\$(VYear)'},CalendarMonthNumber={'\$(VPriorMonth)'}, CalendarMonthName= >}CustomerID)>} distinct CustomerID)

8 Replies
Highlighted
MVP

## Re: Data Intersection Question

There are some solutions for counting new / lost / returning customers here in the forum, for example:

New/Lost/Returning/Loyal Customers

Highlighted
Contributor II

## Re: Data Intersection Question

Hi Swuehl,

First of all many thank's for your help , I read the link and I'm trying to do with this formula, but did not works, could you help to me?

count({<CalendarYear={'\$(VYear)'},CalendarMonthName={'\$(VMonth)'},CUSTACCOUNT

=P({<CalendarYear={'\$(VYear)'},CalendarMonthName={'\$(VPriorMonth)'}>}CUSTACCOUNT)>} distinct CUSTACCOUNT)

Eduard

Highlighted
MVP

## Re: Data Intersection Question

a) that each variable expands to a value and the value match field formats and possible values in fields

b) you are not selecting Jan as VMonth (I think your logic has a flaw at year Change)

c) that you are not selecting on other calendar fields than CalendarYear and CalendarMonthName, since this might render the set expression to an incompatible set (using the modified field values and selected field values).

All in all, it's hard to tell without knowing more details. Any chance you upload a small sample QVW?

Highlighted
Contributor II

## Re: Data Intersection Question

Hi Swuehl

I created a doc and if you can help to me to create the formula. there is a table named did not work

Many thank's and sorry for the headache

Eduard

Highlighted
MVP

## Re: Data Intersection Question

1) Define your VMonth and VPriorMonth variables with a leading equal sign:

=CalendarMonthNumber-1

2) Since you selected in CalendarMonthName, you need to clear this field in the p() function to avoid an incompatible set:

count({<CalendarYear={'\$(VYear)'},CalendarMonthNumber={'\$(VMonth)'},CustomerID=P({<CalendarYear={'\$(VYear)'},CalendarMonthNumber={'\$(VPriorMonth)'}, CalendarMonthName= >}CustomerID)>} distinct CustomerID)

You can also remove the first to set modifier, since they won't change anything compared to your default set:

count({<CustomerID=P({<CalendarYear={'\$(VYear)'},CalendarMonthNumber={'\$(VPriorMonth)'}, CalendarMonthName= >}CustomerID)>} distinct CustomerID)

Highlighted
Contributor II

## Re: Data Intersection Question

Hi Swuehl

Many mnay thank's the problem that i was working was to exclude the CalendarMonthName inside the function...but now I have new problem, when I want to compare january 2015 Vs 2014 this formula did not work, did you know how to solve?

Many many thank's again

Highlighted
MVP

## Re: Data Intersection Question

You can create a MonthIndex field in your script like

Year,

MonthNum,

Year*12+MonthNum AS MonthIndex,

...

MonthIndex should now show consecutive numbers across all years, now operate on MonthIndex to find the previous month in your set analysis.

Highlighted
Contributor II

## Re: Data Intersection Question

Many many thank's

If you come to Barcelona a Paella is waiting you 😉