topic Re: Set Expression in App Development

Set Expression

Wed, 19 Jul 2017 09:21:19 GMT

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

Re: Set Expression

sunny_talwar — Wed, 19 Jul 2017 11:21:16 GMT

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

Re: Set Expression

Wed, 19 Jul 2017 12:08:34 GMT

hey sunny ,

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

Regards

Charlie

Re: Set Expression

sunny_talwar — Wed, 19 Jul 2017 12:17:26 GMT

How big are we talking about?

Re: Set Expression

Wed, 19 Jul 2017 12:22:34 GMT

70 MB somewhere.....

Regards

Charleo

Re: Set Expression

sunny_talwar — Wed, 19 Jul 2017 12:24:05 GMT

That's not bad, go ahead and post it

Re: Set Expression

Wed, 19 Jul 2017 12:46:36 GMT

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

Re: Set Expression

alexandrakfcosta — Wed, 19 Jul 2017 13:40:50 GMT

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

Re: Set Expression

Wed, 19 Jul 2017 13:50:28 GMT

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

something Missing Alex..

regards

Charlie

Re: Set Expression

alexandrakfcosta — Wed, 19 Jul 2017 14:29:56 GMT

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

Re: Set Expression

Thu, 20 Jul 2017 06:14:38 GMT

Hey Alex,

very happy you are trying , y is it not working still, i had attached the sample qvf above , could you please check in it once ..sorry for putting you through trouble Alex.

Regards

charlie

Re: Set Expression

Thu, 20 Jul 2017 07:26:48 GMT

Hey Sunny ,

Did you find any solution Pal.

Regards

charlie

Re: Set Expression

sunny_talwar — Thu, 20 Jul 2017 12:06:22 GMT

I am confused... look at 1st sheet, but change to set analysis in sheet 2 and 3? Can you elaborate what am I looking at within 1st sheet and why? and which object I am working on in Sheet2 or Sheet3? Why both sheets? what is the difference here?

Re: Set Expression

Thu, 20 Jul 2017 12:21:26 GMT

1st sheet its done , i changed some how. it is for ur better understanding , u have to change the second sheet and 3rd sunny. the one i asked is in 3rd sheet KPI. And there is a variable in all the sheet to view count/Amount. i had attached for ur reference.

Regards

charlie

Re: Set Expression

Fri, 21 Jul 2017 06:28:14 GMT

Hey Sunny,

did you understand it .....?

Regards

charlie

Re: Set Expression

Tue, 25 Jul 2017 08:58:39 GMT

HeySunny ,

Any advice on the expression pal...

Regards

Charlie