Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Set Expression

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

15 Replies

Re: Set Expression

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

Not applicable

Re: Set Expression

hey sunny ,

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

Regards

Charlie

Re: Set Expression

How big are we talking about?

Not applicable

Re: Set Expression

70 MB somewhere.....

Regards

Charleo

Re: Set Expression

That's not bad, go ahead and post it

Not applicable

Re: Set Expression

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
New Contributor III

Re: Set Expression

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

Not applicable

Re: Set Expression

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

something Missing Alex..

regards

Charlie

alexandrakfcosta
New Contributor III

Re: Set Expression

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

Community Browser