topic Re: How to add indirect set analysis to an expression in QlikView

How to add indirect set analysis to an expression

Wed, 02 Dec 2015 15:01:58 GMT

Good day,

I´m trying to add indirect set analysis to an expression, the expression looks like this and works fine:

Sum({< DateKey = {"=InYearToDate(DateKey, Today(), 0)",

"=InYearToDate(DateKey, Today(), -1)",

"=InYearToDate(DateKey, Today(), -2)"} >} Sales)

I would like it to take into consideration only stores that had sales in the previous years to get a Like-For-Like comparison, and tried something like this:

Sum({< DateKey = {"=InYearToDate(DateKey, Today(), 0)",

"[StoreName]=P(=InYearToDate(DateKey, Today(), -1)[StoreName])",

"[StoreName]=P(=InYearToDate(DateKey, Today(), -2)[StoreName])"} >} Sales)

But this does not seem to be correct, does anyone have a suggestion on how to add the P() to this expression?

Re: How to add indirect set analysis to an expression

effinty2112 — Fri, 04 Dec 2015 09:26:38 GMT

Hi Gudmundur,

I've not able to test this of course so I apologise for syntax errors but I think you should take your set modifier that works and take the intersection of that with the set of stores that have had sales in the last two years. Something like this:

<DateKey = {"=InYearToDate(DateKey, Today(), 0)","=InYearToDate(DateKey, Today(), -1)",

"=InYearToDate(DateKey, Today(), -2)"} > //Your set modifier

<StoreName = P({<DateKey = {"InYearToDate(DateKey, Today(), -1)"}> + <DateKey = {"InYearToDate(DateKey, Today(), -2)"}>})> // Additional set modifier

Good luck

Re: How to add indirect set analysis to an expression

Fri, 04 Dec 2015 11:16:03 GMT

Thank you Andrew for your response.

I tried what you suggested, but I´m not getting the syntax correct, but here is how I made the expression, note that this is taking 3 years into account:

Sum({<DateKey = {"=InYearToDate(DateKey, Today(), 0)",

"=InYearToDate(DateKey, Today(), -1)",

"=InYearToDate(DateKey, Today(), -2)"}>} Sales) *

<[StoreName]=P({< Sum({< DateKey = {"=InYearToDate(DateKey, Today(), 0)",

"=InYearToDate(DateKey, Today(), -1)",

"=InYearToDate(DateKey, Today(), -2)"} >} Sales)

Regards

Re: How to add indirect set analysis to an expression

effinty2112 — Fri, 04 Dec 2015 15:22:05 GMT

Hello Gudmundur,

Without being able to try this out I can't guarantee the syntax is correct but let's try it. I've added notes that I hope help out.

good luck again!

Sum({

< // open angle bracket to begin new set modifier

[StoreName]=

P({< // open brackets for P expression. Always take form P({< ... >})

DateKey = {

"=InYearToDate(DateKey, Today(), 0)",

"=InYearToDate(DateKey, Today(), -1)",

"=InYearToDate(DateKey, Today(), -2)"}

>}) // close brackets for P expression.

> // closed angle bracket to end new set modifier

}Sales)

Re: How to add indirect set analysis to an expression

Tue, 08 Dec 2015 10:19:51 GMT

Hi Andrew,

I tried this and I get OK on the syntax but it is not working, I still get all stores, not just those that have sales in both or all selected years.

sum({<DateKey={"=InYearToDate(DateKey, Today(), 0)", "=InYearToDate(DateKey, Today(), -1)", "=InYearToDate(DateKey, Today(), -2)"}>

<[StoreName]=P({<DateKey={"=InYearToDate(DateKey, Today(), 0)", "=InYearToDate(DateKey, Today(), -1)", "=InYearToDate(DateKey, Today(), -2)"}>})>} Sales)

If you have any more ideas that would be great, thanks again for your help!

Best regards

Gudmundur.