convert if in to setanalysis

[#1]

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)))

Help Is Appriceated please....   

Thanks

Charleo

[#2]

Hi Sunny,

Here is the sample,

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

Thanks In advance

Regards

Charleo

[vinieme12]

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,

[#4]

No Luck Vineeth..

Thanks

Charleo

[#5]

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

possibly u will get an idea..

Thanks

Charleo

[#6]

Hey Sunny Did you checked the sample dude..

Thanks

Charleo

[#7]

hey vineeth,

check the attached sample once .

Thanks

leo

[sunny_talwar]

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.

[#9]

Thanks Dude

Leo

[MK9885]

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.

[#11]

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

)))

can you write this is set analysis please........like below format please..

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

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

any idea , after loading data , chart is not showing data ....any advice please...

waiting for your reply

Thanks iN advance

Charleo