Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Set Analysis: Search String / concatenate dimensions

My dataset has Group, Month (YYYYMM), Year (YYYY), Renewal Month (MM), and Sales.

I want to make a table with Group and Year as the dimension. The two expressions would be "sum of sales at the Renewal Month" and "sum of sales at the month before the renewal month".

I'm just trying to work on "sum of sales at the Renewal Month" for now by using Set Analysis, but haven't had much luck.

For example, I've tried =sum({<Month={"='2012' & [Renewal Month]">} Sales). It seems that the concatenation is not working in the search string, as I get the equivalent of =sum(Sales) with this expression.  

(Ultimately, 2012 would be the Year variable and there'd have to be some logic for if renewal month is 01, then go back one year and pick "(Year -1)12")

Sorry, I'm not able to upload the file.

Thanks for any help on this.

10 Replies
mov
Esteemed Contributor III

Re: Set Analysis: Search String / concatenate dimensions

If your field Month is text, and has format like '2012Jan', and [Renual Month] has format like 'Jan', then this should work:

=sum({<Month={"$(='2012' & [Renewal Month])">} Sales).

Otherwise, to help you I need to know the exact format of your data.

Regards,

Michael

Not applicable

Re: Set Analysis: Search String / concatenate dimensions

Month is a number. For example, 201202. Renewal month is text, 02. The concatenation is going to be text then. What's the way to cast the concatenation into a number?

mov
Esteemed Contributor III

Re: Set Analysis: Search String / concatenate dimensions

In this case my expression should work as well.  If it still doesn't, try add num#():

=sum({<Month={"$(=num#('2012' & [Renewal Month]))">} Sales)

Not applicable

Re: Set Analysis: Search String / concatenate dimensions

Hmmm.. That isn't doing the trick. Each row gets value of 0.

(I noticed a "}" is missing from your expression, but that didn't fix it)

The fields are part of a bigger dataset. Group and Renewal Month are on one table, Sales and Month are on their own tables. There is a Linker table that joins these fields and this model has been used and tested for a while, so the joining is working. Perhaps in this case it is prohibiting this expression from working?

mov
Esteemed Contributor III

Re: Set Analysis: Search String / concatenate dimensions

May I see the fixed expression?  Want to make sure where did you add the missing "}".

I don't think that the structure you described is a problem.  Most likley something is missing in this expression.  It would be helpful to see an example - reduced file with scrambled data.

Not applicable

Re: Set Analysis: Search String / concatenate dimensions

sum({<Month={"$(=num#('2012'& [Renewal Month]))"}>}Sales) 

MVP
MVP

Re: Set Analysis: Search String / concatenate dimensions

Is [Renewal Month] unambiguous in global context?

I mean, it is not depending on Group or Year, and you get a value back if you input =[Renewal Month] in a text box?

mov
Esteemed Contributor III

Re: Set Analysis: Search String / concatenate dimensions

Yes, this looks right.  Waht if you replace set with if:

sum(if(Month=num#('2012'& [Renewal Month]), Sales))

Not applicable

Re: Set Analysis: Search String / concatenate dimensions

Swuehl: Each Group is assigned exactly one Renewal Month and this is consistent from year to year

Michael: Your expression got a non-zero answer for most groups, but the renewal month sales is greater than total sales for some, so some double counting taking place. I will try to dig into more, but wondering if you know why.

Group IDTotal SalesRenewal Month Sales
1103,654261,281
227,609127,403
332,804156,250
420,95260,761
5231,351350,640
659,24355,456
75,4010
844,37833,505
944,42042,219
1057,7660
11147,699142,240
1222,1330
13115,0230
14880,228748,088
15125,7110
16636,693607,004
1783,83471,196
18164,377152,704
19259,879259,344
20193,482192,274
2160,21758,725
22172,322160,515
2395,51483,730
242,280,0041,905,258
2523,47415,246
26663,501853,616
Community Browser