Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am struggling to convert an IF THEN expression to Set Analysis.
I have a sample below.
Original expression:
sum( if(YM = EFFECTIVEYM, Sales))
Set Anaysis expression:
sum( {$< YM = P(EFFECTIVEYM) >} Sales)
This set analysis works fine only of there is 1 YM value selected. As soon as I select more than 1 YM value, then the figures are overstated.
Is there another way of writing set analysis where FIELD = FIELD?
Thanks
Yatin
maybe
here, page 14, Using two fields
Set Analysis: syntaxes, examples
or you can add a flag in the script and use that flag in set analysis
Do they come from the same table in the back end? If they do... may be create a flag in the script
If(YM = EFFECTIVEYM, 1, 0) as YMFlag
and then this
Sum({$<YMFlag = {'1'}>} Sales)
Hi,
I have reviewed this pdf, but don't understand what it means by creating an autonumber() key? How do I create this? Is it something I need to do in the backend script?
Also, these 2 fields are in separate tables, so I cannot create a Flag field in the backend.
"Attention: the searched dimension cannot be also in the boolean condition. If needed, create an integer key with Autonumber(). "
Thanks.
1)
The pdf suggestion works if the flelds are in the same table and
you have a primary key in the table; if not, may be you can create in the script a primary key with the autonumber function
2)
Also, these 2 fields are in separate tables, so I cannot create a Flag field in the backend.
Sometimes is possible if there is a relation between the 2 tables
ATable
AKey, YM
1, 1-1-2018
2, 2-1-2018
BTable
AKey, BKey, EFFECTIVEYM, Flag
1, a, 10-1-2018,
1, b, 1-1-2018, 1 the ATable YM of AKey 1 is 1-1-2018 = EFFECTIVEYM
2, c, 1-1-2017,
2, d, 1-1-2017,
applymap or a join can be useful to calculate the flag
3)
sum if can be a problem with many rows (performance)
4)
maybe you can post your .qvw or a small example