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: 
Not applicable

Forcing the Set analysis Context

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.

5 Replies
hic
Former Employee
Former Employee

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

rbecher
MVP
MVP

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

Astrato.io Head of R&D
Not applicable
Author

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.

Not applicable
Author

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.

cleblois
Contributor III
Contributor III

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