Set Expression

[#1]

sum(if(vColl='A' and YearMonth = text(Timestamp(Today,'YYYYMM')),L1SBSrcTgtAmount+L2SBSrcTgtAmount,

if(vColl='C' and   YearMonth = text(Timestamp(Today,'YYYYMM')) ,L1SBSrcTgtCount+L2SBSrcTgtCount)))

someone please change in to setanalysis.

thanks In advance

Regards

charlie

[sunny_talwar]

Would you be able to share a sample where you doing this? What we provide, may or may not work, it would be helpful to see a sample where you are using the if and convert and test the set analysis

[#3]

hey sunny ,

file is big in size dude...ru sure u look in to it , if i send sample .

Regards

Charlie

[sunny_talwar]

How big are we talking about?

[#5]

70 MB somewhere.....

Regards

Charleo

[sunny_talwar]

That's not bad, go ahead and post it

[#7]

please have a look at 1st sheet , if possible change if in to set in 2nd and 3rd sheets dude. the one mentioned in the discussion is in third sheet its a KPI...

Regards

Charlieo

[alexandrakfcosta]

Hi Charlie,

Your IF clause has two arguments, the first one is about the value of vColl and the second one is about the YearMonth value. To change them to Set Analysis, we have to do the folowing:

vColl='A'    turns to    vColl={'A'}

vColl='B'    turns to    vColl={'B'}

YearMonth = text(Timestamp(Today,'YYYYMM'))    turns to    YearMonth={$(=Date(Today(), 'YYYYMM'))}

So we will have two set analysis conditions:

{$<vColl={'A'}, YearMonth={$(=Date(Today(), 'YYYYMM'))}>}

{$<vColl={'B'}, YearMonth={$(=Date(Today(), 'YYYYMM'))}>}

Now, let's talk about the Sum. As set analysis "isolate" the sets, we can use the sum of 4 different subsets.

Sum(L1SBSrcTgtAmount) + Sum(L2SBSrcTgtAmount)      =>  first set analysis condition

Sum(L1SBSrcTgtCount) + Sum(L2SBSrcTgtCount)           =>  second set analysis condition

So, the solution you are trying to achieve is something like this:

Sum({$<vColl={'A'}, YearMonth={$(=Date(Today(), 'YYYYMM'))}>} L1SBSrcTgtAmount) +

Sum({$<vColl={'A'}, YearMonth={$(=Date(Today(), 'YYYYMM'))}>} L2SBSrcTgtAmount) +

Sum({$<vColl={'C'}, YearMonth={$(=Date(Today(), 'YYYYMM'))}>} L1SBSrcTgtCount) +

Sum({$<vColl={'C'}, YearMonth={$(=Date(Today(), 'YYYYMM'))}>} L2SBSrcTgtCount)

I hope I could help!

Regards,

Alexandra

[#9]

Thanks Alex, but no luck , i appreciate your Effort .

something Missing Alex..

regards

Charlie

[alexandrakfcosta]

Yes. It is missing the ' in the date clause and I also optimised the formula:

Sum({$<vColl={'A'}, YearMonth={'$(=Date(Today, 'YYYYMM'))'}>} L1SBSrcTgtAmount + L2SBSrcTgtAmount) +

Sum({$<vColl={'C'}, YearMonth={'$(=Date(Today, 'YYYYMM'))'}>} L1SBSrcTgtCount + L2SBSrcTgtCount)

But I also understand that your IF clause cannot be replaced by this set analysis because what you want at the end is the sum of one set OR another. The formula that I provided SUMs both subsets. You can check that the second part of the formula: Sum({$<vColl={'C'}, YearMonth={'$(=Date(Today, 'YYYYMM'))'}>} L1SBSrcTgtCount + L2SBSrcTgtCount) gives you the same result of your original formula.

So, what we need is a mix from your formula and mine, like this one below:

If(vColl='A', Sum({$<YearMonth={'$(=Date(Today, 'YYYYMM'))'}>} L1SBSrcTgtAmount + L2SBSrcTgtAmount),

If(vColl='C', Sum({$<YearMonth={'$(=Date(Today, 'YYYYMM'))'}>} L1SBSrcTgtCount + L2SBSrcTgtCount))).

Best Regards,

Alexandra