19 Replies Latest reply: Jun 23, 2017 6:00 AM by Charlie Leo

# convert if in to setanalysis

Hi All ,

Help me convert the below if statement in to setanalysis.

sum(if(vJCEKPI1='A' and Date>=DateCurrPeriodStart and DorC='C',DCAmount,

if(vJCEKPI1='C' and Date>=DateCurrPeriodStart and DorC='C',DCCount)))

Thanks

Charleo

• ###### Re: convert if in to setanalysis

May be this

Sum({<vJCEKPI1 = {'A'}, Date = {"\$(='>=' & DateCurrPeriodStart)"}, DorC = {'C'}>} DCAmount) +

Sum({<vJCEKPI1 = {'C'}, Date = {"\$(='>=' & DateCurrPeriodStart)"}, DorC = {'C'}>} DCCount)

• ###### Re: convert if in to setanalysis

Its not working dude...there is a variable to select 'A','C', its not reflecting sunny

Thanks

Charleo

• ###### Re: convert if in to setanalysis

Would you be able to share a sample?

• ###### Re: convert if in to setanalysis

Hi Sunny,

Here is the sample,

try changing the if statements in to setanalysis....and attached extension variable , check if it reflects

Regards

Charleo

• ###### Re: convert if in to setanalysis

Hey Sunny Did you checked the sample dude..

Thanks

Charleo

• ###### Re: convert if in to setanalysis

I have two problems

1) The huge size of the file

2) I am unable to use desktop version of Qlik Sense and I can't really install the extension to the server...

I hope someone else might be able to help better.

• ###### Re: convert if in to setanalysis

Thanks Dude

Leo

• ###### Re: convert if in to setanalysis

=Sum({\$<vJCEKPI1={'A'},Date={">=DateCurrPeriodStart"},DorC={'C'}>}DCAmount)+

Sum({\$<vJCEKPI1={'C'},Date={">=DateCurrPeriodStart"},DorC={'C'}>}DCCount)

hope it helps

• ###### Re: convert if in to setanalysis

Its not working dude,

Thanks

Charleo

• ###### Re: convert if in to setanalysis

what is the definition for vJCEKPI1

• ###### Re: convert if in to setanalysis

'C' is the defination

regards

Charleo

• ###### Re: convert if in to setanalysis

Try below

Sum({<DCAmount = {"=\$(vvJCEKPI1)='A'"}, Date = {"\$(='>=' & DateCurrPeriodStart)"}, DorC = {'C'}>} DCAmount) +

Sum({<DCCount= {"=\$(vvJCEKPI1)='C'"}, Date = {"\$(='>=' & DateCurrPeriodStart)"}, DorC = {'C'}>} DCCount)

DCAmount = {"=\$(vvJCEKPI1)='A'"}  << Here instead of DCAmount  or DCCount , you should Ideally be using a Key field that identifies each row separately,

• ###### Re: convert if in to setanalysis

No Luck Vineeth..

Thanks

Charleo

• ###### Re: convert if in to setanalysis

Hey Vineeth I had attach the sample file and extension variable ...please look in to it ..

possibly u will get an idea..

Thanks

Charleo

• ###### Re: convert if in to setanalysis

Hi Vineeth ,

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

if(vColl='C' and YearMonth = text(Timestamp(Today,'YYYYMM')),L1SBSrcActCount+L2SBSrcActCount

)))

Sum({<DCAmount = {"=\$(vvJCEKPI1)='A'"}, Date = {"\$(='>=' & DateCurrPeriodStart)"}, DorC = {'C'}>} DCAmount) +

Sum({<DCCount= {"=\$(vvJCEKPI1)='C'"}, Date = {"\$(='>=' & DateCurrPeriodStart)"}, DorC = {'C'}>} DCCount)

Charleo

• ###### Re: convert if in to setanalysis

May be you only need \$ expansion for variables

Sum({<\$(vJCEKPI1) = {'A'}, Date = {"\$(='>=' & DateCurrPeriodStart)"}, DorC = {'C'}>} DCAmount) +

Sum({<\$(vJCEKPI1) = {'C'}, Date = {"\$(='>=' & DateCurrPeriodStart)"}, DorC = {'C'}>} DCCount)

• ###### Re: convert if in to setanalysis

Hey Vineeth,

Its not working dude.

Thanks

charleo

• ###### Re: convert if in to setanalysis

hey vineeth,

check the attached sample once .

Thanks

leo

• ###### Re: convert if in to setanalysis

Not an expert but your data model isn't start schema nor it is snow flake...

One of the table is missing link

Ex: Branch Dates has no link to any of the table, seem like individual table.

and you using DateCurrPeriodStart from Brach dates which is not linked to either calendar or fact

and if(vJCEKPI1='C' <<<<< this part is not stored in variable only A is stored.