topic Re: Forcing the Set analysis Context in QlikView

Forcing the Set analysis Context

Wed, 21 Oct 2015 16:16:22 GMT

Hello,

Please find attached a QlikView app that illustrate my question.

I am trying to get the count of the new products that appeared in one year compared to previous one.

My expression works well for this two years (2011, 2010) because it hard coded :

Count( DISTINCT {$ < Product= P({<Year={'2011'}>} Product)- P({<Year={'2010'} >} Product)>} Product)

How can we use it for every values? Something like : Count( DISTINCT {$ < Product= P({<Year={'$(=Max(Year))'}>} Product)- P({<Year={'$(=Max(Year)-1)'} >} Product)>} Product)? In The Set analysis Max(Year) always return 2012 even if i have the year as dimension included in the chart!

In my case it is impossible to use Above()..

Thank you for help.

Re: Forcing the Set analysis Context

hic — Wed, 21 Oct 2015 17:29:36 GMT

No, you cannot do this with Set Analysis. The Set Analysis is like a selection that is made before all the cells of the table are evaluated, and you want the evaluation to be different for different years.

I would do it in the script. You could for example add the following lines after your loaded table (named tmp):

Load *,

If(Product=Peek(Product),0,1) as IsNew

Resident tmp

Order By Product, Year ;

Drop Table tmp;

HIC

Re: Forcing the Set analysis Context

rbecher — Wed, 21 Oct 2015 19:52:10 GMT

Very very challenging..

Before I came to this, I had already given up twice:

SubStringCount(Concat(Aggr(Product & ':' & Concat(DISTINCT  Year, ','), Product), '/'), ':')

See attached..

You have to think twice the corner

- Ralf

PS: this reminds me on the "old" basket analysis problem which could probably solved with this approach

Re: Forcing the Set analysis Context

Thu, 22 Oct 2015 09:00:38 GMT

Hi Henric,

Thanks a lot for your reply, but unfortunaly this very clean solution doesn't work in my case because my result depends on the selections that i could have, ex a new product for my customer 1, is not the same for another one. So the solution if it exists have to be on the display not in the script.

Thank you Henric, i hope that you'll have another suggestion.

Hamid.

Re: Forcing the Set analysis Context

Thu, 22 Oct 2015 09:14:44 GMT

Hi Ralf,

Yes a very big challenge this's why i went to catch the best

I spent 2h to understand your solution, it looks very smart but difficult to understand.

Why does the Concat Product year take only the new one year by year?

A) May be because of the 'Distinct' parameter --> No because i droped it and i got the same result

B) Maybe Because of the Magical and Unpredictable Aggr ?

I have saw that in the example that you sent, you missed P2 as new in 2013, beacause he appeared in 2010, we lost it in 2012 and he reappeared in 2013 so it's a new product

But i doubt that i can readapte this solution for my other case, so i need to count the Lost too no luck

Hope you'll find a time to reply me

Best Regards,

Hamid.

Re: Forcing the Set analysis Context

cleblois — Fri, 20 Nov 2015 07:48:51 GMT

Disclaimer: In advance I'm apologizing for the ugliness of the solution. Sometimes you need to do what you do for the sake of having a solution

Hi,

Below solution requires some yearly maintenance. If anyone has an idea to make it better, I'd be very happy to get it.

Below are the formulas I've used to make it work:

* Joiners:

If (Year_QVW=2013,Count( DISTINCT {$ < DPN= P({<Year_QVW={'2013'}>} DPN)- P({<Year_QVW={'2012'}>} DPN)>} DPN),

If (Year_QVW=2014,Count( DISTINCT {$ < DPN= P({<Year_QVW={'2014'}>} DPN)- P({<Year_QVW={'2013'}>} DPN)>} DPN),

If (Year_QVW=2015,Count( DISTINCT {$ < DPN= P({<Year_QVW={'2015'}>} DPN)- P({<Year_QVW={'2014'}>} DPN)>} DPN),

0)))

* Leavers:

If (Year_QVW=2012,Count( DISTINCT {$ < DPN= P({<Year_QVW={'2012'}>} DPN)- P({<Year_QVW={'2013'}>} DPN)>} DPN),

If (Year_QVW=2013,Count( DISTINCT {$ < DPN= P({<Year_QVW={'2013'}>} DPN)- P({<Year_QVW={'2014'}>} DPN)>} DPN),

If (Year_QVW=2014,Count( DISTINCT {$ < DPN= P({<Year_QVW={'2014'}>} DPN)- P({<Year_QVW={'2015'}>} DPN)>} DPN),

0)))

Christophe