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

[Kafka_polka_dot]

Hi Matheus, sorry for a delayed reply, I was on vacation. Thank you very much for the suggestion, but I'm afraid, it will not work for my specific case.

My main problem is that the date range for the chart should be set by the filters in an alternate state, while the dimensions 'customer' and 'database' should remain available in the default state.

The chart 'TOP 10 new customers by sales' is made with a master measure New_customer_sales where I am using calculations with alternate state in set analysis. But the chart itself works properly only if I also set it also to the alternate state. Do you know, why does it happen?

Is there a way to set this chart in 'inherited' or 'default' state so that the selection of the customer in the chart affects all the other elements of the dashboard in the inherited/default state (e.g. KPI)?

Is there a way to make a chart available for the filters in the inherited/default state (e.g. filter 'Database')?

[Kafka_polka_dot · Accepted solution]

I found a mistake in my set expression analysis expressions, which is connected to the date range selection.

The correct formulas should include the alternate state in the Date field. E.g., here is a formula for previous 3 month sales absed on the date range from v1 selection:

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

This helps to have both the calculations and final chart in default state while only get dates from v1 alternate state selection.