Alternate States and Filtering

[Kafka_polka_dot]

Hi dear members of Qlik community,

I am new to Qlik and working on the edits for already existing dashboard. I got a requirement to set a chart with top-10 new customers. New customer is a customer who had sales in the latest selected month, but had no sales in the previous 3 months.

I have set 3 calculations.

--MTD sum of sales
Sum({[V1]<OrderDate={">=$(=Date(MonthStart(Max(OrderDate)), 'YYYY-MM-DD'))<=$(=Max(OrderDate))"}, [DATENBANK] = $::[DATENBANK]>} sales)

--previous 3 months
Sum({[V1]<OrderDate={">=$(=Date(MonthStart(AddMonths(Max(OrderDate), -3)), 'YYYY-MM-DD'))<=$(=Date(MonthEnd(AddMonths(Max(OrderDate), -1)), 'YYYY-MM-DD'))"}, [DATENBANK] = $::[DATENBANK]>} sales)

--new customers sum of sales
IF(([MTD_Sales]>0 and [PM_3_Sales] = 0), [MTD_Sales])

So now I can make a chart with top-10 new customers using the measue "new customers sum of sales".

My problem is that despite the fact, that alternate state are set in the calculated fields "MTD sum of sales" and "previous 3 months", the results for the top-10 new customers are only showed correctly if I set this chart in V1 as well. When I do that, I can not use the customers names as filters for the rest of the dashboard, since thie dimension is only working in the V1 state, and does not affect the inherited state.

Caould you please give me advice, how can I have the MTD and Previous 3 month calulations set in the slternate state, but the chart with top-10 new customers - in the "inherited" state?

[MatheusC]

@Kafka_polka_dot 

To use the default state, in your setanalysis try inserting as the reference below:

{<customers=$::customers>}

- Matheus

[Kafka_polka_dot]

Hi Mateus, thank you for reply! 

Unfortunately, when I add the customer name to set analysis, it makes it available for the filters, but does not affect the selection if I select customers from the chart. It is shown as V1 selection.

[Kafka_polka_dot]

[MatheusC]

I'm not sure if I fully understood your requirement, but do you want both the inherited and alternative filters to impact your chart, with the chart applied as the alternative state?

This is not something I have done yet, but maybe something like this should work based on some tests I did to help with the solution.

// modifier P() – For the possible values ​​of the customers field and the intersection * with the alternative state

{< customers = P({$} customers)*P({V1})>}

Regards

[Kafka_polka_dot]

Thanks for reply, p() approach is also not quite working.

I need to set a chart with top-10 new customers. The calculation for the top-10 new customers: 

IF(([MTD_Sales]>0 and [PM_3_Sales] = 0), [MTD_Sales])

 This calulation is using the two calulations which already have the alternate state V1 date range: MTD_Sales:

Sum({[V1]<OrderDate={">=$(=Date(MonthStart(Max(OrderDate)), 'YYYY-MM-DD'))<=$(=Max(OrderDate))"}, [DATENBANK] = $::[DATENBANK]>} sales)

and PM_3_Sales:

Sum({[V1]<OrderDate={">=$(=Date(MonthStart(AddMonths(Max(OrderDate), -3)), 'YYYY-MM-DD'))<=$(=Date(MonthEnd(AddMonths(Max(OrderDate), -1)), 'YYYY-MM-DD'))"}, [DATENBANK] = $::[DATENBANK]>} sales)

I wonder, if it is possible to:

- Either set the top-10 new customers chart to the "inherited" state (in the current syntax it is not working)

- Or set the top-10 new customers chart to the V1 state, but make the selection of the customer in this chart affecting the objects outside the V1 state.

Hope it makes more sense now. If not, could it help if I create a test qlik file? 

[MatheusC]

I believe I am starting to understand what you need. I thought of something like this with an if() condition for customers with purchases in the current month and no purchases in the last 3 months. 
**Change according to information coming from your table**

//with purchases in the current month
if(count({<sales = {">$(=0)"}, OrderDate={">=$(=Date(MonthStart(Max(OrderDate)), 'YYYY-MM-DD'))<=$(= Max(OrderDate))"} >} distinct customers) = 1
and 
//no purchases in the last 3 months
count({<sales = {">$(=0)"}, OrderDate={">=$(=Date(MonthStart(AddMonths(Max(OrderDate), -3)), 'YYYY-MM-DD') )<=$(=Date(MonthEnd(AddMonths(Max(OrderDate), -1)), 'YYYY-MM-DD'))"} >} distinct customers) = 0,
sales,0)

Then, apply the limitation to the Dimension field, for values Top 10.

Feel free to send the qvf file if you haven't reached the solution.


Regards