topic Re: convert if in to setanalysis in App Development

convert if in to setanalysis

Wed, 07 Jun 2017 11:31:01 GMT

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

Re: convert if in to setanalysis

sunny_talwar — Wed, 07 Jun 2017 11:36:07 GMT

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

tajmohamed30 — Wed, 07 Jun 2017 11:55:50 GMT

=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

Wed, 07 Jun 2017 12:08:05 GMT

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

Thanks

Charleo

Re: convert if in to setanalysis

Wed, 07 Jun 2017 12:08:41 GMT

Its not working dude,

Thanks

Charleo

Re: convert if in to setanalysis

sunny_talwar — Wed, 07 Jun 2017 12:10:19 GMT

Would you be able to share a sample?

Re: convert if in to setanalysis

vinieme12 — Wed, 07 Jun 2017 12:12:50 GMT

what is the definition for vJCEKPI1

Re: convert if in to setanalysis

vinieme12 — Wed, 07 Jun 2017 12:26:11 GMT

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

Wed, 07 Jun 2017 12:35:06 GMT

Hey Vineeth,

Its not working dude.

Thanks

charleo

Re: convert if in to setanalysis

Wed, 07 Jun 2017 12:36:05 GMT

'C' is the defination

regards

Charleo

Re: convert if in to setanalysis

Wed, 07 Jun 2017 12:49:19 GMT

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

Re: convert if in to setanalysis

vinieme12 — Wed, 07 Jun 2017 13:25:45 GMT

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

Wed, 07 Jun 2017 13:32:57 GMT

No Luck Vineeth..

Thanks

Charleo

Re: convert if in to setanalysis

Wed, 07 Jun 2017 13:36:37 GMT

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

Thu, 08 Jun 2017 06:32:19 GMT

Hey Sunny Did you checked the sample dude..

Thanks

Charleo

Re: convert if in to setanalysis

Thu, 08 Jun 2017 11:13:54 GMT

hey vineeth,

check the attached sample once .

Thanks

leo

Re: convert if in to setanalysis

sunny_talwar — Thu, 08 Jun 2017 11:23:13 GMT

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

Thu, 08 Jun 2017 12:09:41 GMT

Thanks Dude

Leo

Re: convert if in to setanalysis

MK9885 — Thu, 08 Jun 2017 12:38:10 GMT

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.

Re: convert if in to setanalysis

Fri, 23 Jun 2017 10:00:27 GMT

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