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: 
coloful_architect
Creator II
Creator II

qlik pivot table / chart to show dimensions with conditions that measure equals particular value and within particular time

Hi there,

want to build a pivot table or water fall chart to only show those discontented account/customers:

I have :

account 

sales (measure)

sales time,

how I can edit expression to showcase discontinued accounts within 6 months and 12 months.

To put another way, I want to list all those accounts that does not have any sales within 6 months or 12 months

Thanks 

 

 

2 Solutions

Accepted Solutions
coloful_architect
Creator II
Creator II
Author

 GrayGiles, much appreciated

Three followed questions:

1. do I have to use set statement for both dimension and measure?  or just either one of them

2. Tried to insert your measure, maybe I missed something, it did not go thru.  would you please make a new expression with 

[Customer Full Name]

[Contract Sales]

[Time]

3.  I am trying to tease out a waterfall chart showing all those customers with 0 sales during past 3 month with variances against their previous 3 months sales (6month as of current date).

I created a variable for total 3 month sales and put into set expression in order to filter out all those customers with 0 sales of 3 month. However, it works for some customers but not all of them. Still some customers with positive sales are there. Am I missing something?  Here below is my expression for measure: 

Num(Sum({$<v3monthsales={"=0"},SaleYear=,SaleQuarterYear=, SaleMonthYear=,SaleDate = {">=$(=addmonths(Monthstart((vSaleMaxDate)),-3))<=$(=addmonths(Monthend((vSaleMaxDate)),-1))"} >}[Contract Sales])
-
Sum({$<SaleYear=,SaleQuarterYear=, SaleMonthYear=,SaleDate = {">=$(=addmonths(Monthstart((vSaleMaxDate)),-6))<=$(=addmonths(Monthend((vSaleMaxDate)),-4))"} >}[Contract Sales]),'$#,##0;($#,##0)')

View solution in original post

GaryGiles
Specialist
Specialist

@coloful_architect 

1)  In general, no, you don't have to use both.  You could use one or the other.  But, it depends on what else you want to show in your table or chart.

2)  

=aggr(only({1<[Customer Full Name]-={'=sum({1<[Time]={">=$(=AddMonths(Today(),-6))"}>} [Contract Sales])>0'}>} [Customer Full Name]), [Customer Full Name])

=sum({$<[Customer Full Name]-={'=sum({1<[Time]={">=$(=AddMonths(Today(),-6))"}>} [Contract Sales])>0'}>} [Contract Sales])

3)  You can't use a variable as the left argument in set analysis.

You might try this as your Dimension.

=aggr(only({1<[Customer Full Name]-={'=sum({1<[Time]={">=$(=AddMonths(Today(),-3))"}>} [Contract Sales])>0'}>} [Customer Full Name]), [Customer Full Name])

and this as your measure:

=sum({$<[Time]={">=$(=AddMonths(Today(),-6)<$(=AddMonths(Today(),-3))"}>} [Contract Sales])

The Dimension will filter out customers with sales in the last 3 months and the measure will calculate the sales for the remaining customers (those with no sales in the last 3 months) in the periods 4 through 6.  This would affectively be the variance since the 3 month sales is 0.

 

View solution in original post

3 Replies
GaryGiles
Specialist
Specialist

@coloful_architect 

If you want to limit the accounts in a dimension expression, you would use: 

=aggr(only({1<account-={'=sum({1<[sales time]={">=$(=AddMonths(Today(),-6))"}>} measure)>0'}>} account), account)

If you want to limit the accounts in a measure expression, you would use:

=sum({$<account-={'=sum({1<[sales time]={">=$(=AddMonths(Today(),-6))"}>} measure)>0'}>} measure)

 

Note the minus sign between account and = in the set analysis.  It's kind of hard to see, but it it critical to getting the expressions to work properly.

This is for sales within 6 months.  To get Also, you may need to adjust the formula to calculate the date comparison, depending on the format of [sales time].

coloful_architect
Creator II
Creator II
Author

 GrayGiles, much appreciated

Three followed questions:

1. do I have to use set statement for both dimension and measure?  or just either one of them

2. Tried to insert your measure, maybe I missed something, it did not go thru.  would you please make a new expression with 

[Customer Full Name]

[Contract Sales]

[Time]

3.  I am trying to tease out a waterfall chart showing all those customers with 0 sales during past 3 month with variances against their previous 3 months sales (6month as of current date).

I created a variable for total 3 month sales and put into set expression in order to filter out all those customers with 0 sales of 3 month. However, it works for some customers but not all of them. Still some customers with positive sales are there. Am I missing something?  Here below is my expression for measure: 

Num(Sum({$<v3monthsales={"=0"},SaleYear=,SaleQuarterYear=, SaleMonthYear=,SaleDate = {">=$(=addmonths(Monthstart((vSaleMaxDate)),-3))<=$(=addmonths(Monthend((vSaleMaxDate)),-1))"} >}[Contract Sales])
-
Sum({$<SaleYear=,SaleQuarterYear=, SaleMonthYear=,SaleDate = {">=$(=addmonths(Monthstart((vSaleMaxDate)),-6))<=$(=addmonths(Monthend((vSaleMaxDate)),-4))"} >}[Contract Sales]),'$#,##0;($#,##0)')

GaryGiles
Specialist
Specialist

@coloful_architect 

1)  In general, no, you don't have to use both.  You could use one or the other.  But, it depends on what else you want to show in your table or chart.

2)  

=aggr(only({1<[Customer Full Name]-={'=sum({1<[Time]={">=$(=AddMonths(Today(),-6))"}>} [Contract Sales])>0'}>} [Customer Full Name]), [Customer Full Name])

=sum({$<[Customer Full Name]-={'=sum({1<[Time]={">=$(=AddMonths(Today(),-6))"}>} [Contract Sales])>0'}>} [Contract Sales])

3)  You can't use a variable as the left argument in set analysis.

You might try this as your Dimension.

=aggr(only({1<[Customer Full Name]-={'=sum({1<[Time]={">=$(=AddMonths(Today(),-3))"}>} [Contract Sales])>0'}>} [Customer Full Name]), [Customer Full Name])

and this as your measure:

=sum({$<[Time]={">=$(=AddMonths(Today(),-6)<$(=AddMonths(Today(),-3))"}>} [Contract Sales])

The Dimension will filter out customers with sales in the last 3 months and the measure will calculate the sales for the remaining customers (those with no sales in the last 3 months) in the periods 4 through 6.  This would affectively be the variance since the 3 month sales is 0.