Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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.
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.
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