Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
hey sunny ,
file is big in size dude...ru sure u look in to it , if i send sample .
Regards
Charlie
How big are we talking about?
70 MB somewhere.....
Regards
Charleo
That's not bad, go ahead and post it
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
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
Thanks Alex, but no luck , i appreciate your Effort .
something Missing Alex..
regards
Charlie
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