topic Re: How to compare two different field in set expression? in QlikView

How to compare two different field in set expression?

anindya_manna — Wed, 25 Nov 2020 16:16:04 GMT

Hello Experts,

I have a table like below.

Process Date	CAMPAIGN_START_DATE	Sales	Agent ID
1/1/2018	2/1/2015 12:00:00 AM	500	111
1/2/2018	5/1/2015 12:00:00 AM	600	222
1/3/2018	5/13/2015 12:00:00 AM	700	333
1/4/2018	5/21/2015 12:00:00 AM	800	444
1/5/2018	5/29/2015 12:00:00 AM	900	555
1/6/2018	6/2/2015 12:00:00 AM	1000	666
1/7/2018	6/3/2015 12:00:00 AM	1100	777
1/8/2018	6/9/2015 12:00:00 AM	1200	888
1/9/2018	6/18/2015 12:00:00 AM	1300	999

Below code not working for me.

avg({$<[Process Date]=P({">=$(=addmonths(Max(CAMPAIGN_START_DATE),-6)) <=$(=date(Max(CAMPAIGN_START_DATE),'MM/DD/YYYY'))"})>}[Sales Amt])

I think two different field cant be compare in set exp.So please suggest any alternative idea inclding IF condition.

I dont have access to the script part so cant create any additional flag

Re: How to compare two different field in set expression?

HarishG — Thu, 05 Apr 2018 12:48:54 GMT

Hi,

Can you explain more on your actual requirement?

Re: How to compare two different field in set expression?

olivierrobin — Thu, 05 Apr 2018 12:49:24 GMT

hello

be careful f the format of date fields

try to put the formulas in text boxes to verify that the fields have correct format

i wouls suppose that Process Date is not in M/D/YYYY format

Re: How to compare two different field in set expression?

sunny_talwar — Thu, 05 Apr 2018 12:55:18 GMT

What exactly are you trying to get and what object?

Re: How to compare two different field in set expression?

anindya_manna — Thu, 05 Apr 2018 13:23:33 GMT

I am trying to get Avg sales from 6 months before campaign start date for each agent.

Re: How to compare two different field in set expression?

anindya_manna — Thu, 05 Apr 2018 13:23:50 GMT

I am trying to get Avg sales from 6 months before campaign start date for each agent in a table

Re: How to compare two different field in set expression?

vishsaggi — Thu, 05 Apr 2018 14:08:14 GMT

hello Anindya,

Few points here to clarify.

1. Your date formats between Processed Date and Campaign Start Date are different we need to convert them. However, your expr will show 0 or nothing coz your dates are not between the processed dates. Could be that you just gave sample data which is not matching.

2. Your sales field name is different form the data sample to that in the expression. Like in your expr you have [Sales Amt] but your sample data set has a field name Sales.

However, I have created few date ID's to use them in your set analysis please have a look if this is of any help.

Can we know why you are using P() here just wanted to understand.

Retrospective:
LOAD *, Floor(Date#(ProcessDate, 'M/D/YYYY')) AS ProcessDtID,
Floor(Date(Date#(CAMPAIGN_START_DATE, 'M/D/YYYY hh:mm:ss ttt'), 'M/D/YYYY')) AS CampaignDtID,
Date(Date#(CAMPAIGN_START_DATE, 'M/D/YYYY hh:mm:ss ttt'), 'M/D/YYYY') AS CampaignStartDt INLINE [
ProcessDate, CAMPAIGN_START_DATE, Sales, Agent ID
1/1/2018, 2/1/2015 12:00:00 AM, 500, 111
1/2/2018, 5/1/2015 12:00:00 AM, 600, 222
1/3/2018, 5/13/2015 12:00:00 AM, 700, 333
1/4/2018, 5/21/2015 12:00:00 AM, 800, 444
1/5/2015, 5/29/2015 12:00:00 AM, 900, 555
1/6/2015, 6/2/2015 12:00:00 AM, 1000, 666
1/7/2015, 6/3/2015 12:00:00 AM, 1100, 777
1/8/2018, 6/9/2015 12:00:00 AM, 1200, 888
1/9/2018, 6/18/2015 12:00:00 AM, 1300, 999
];

I used text object to check Avg sales. Changed few Process Dates to 2015 as all of them were in 2018 which always display 0 for Avg. so changed few dates that we can get some values. Check screenshot below.

Re: How to compare two different field in set expression?

sasiparupudi1 — Thu, 05 Apr 2018 14:27:34 GMT

I think the problem you have is you have strings in date than numeric dates..

May be try like below

avg({<ProcessDate={">=$(=Date(Addmonths(Floor(Date#(MAXString(CAMPAIGN_START_DATE), 'M/D/YYYY hh:mm:ss ttt')),-6), 'M/D/YYYY'))"}>}Sales)

Re: How to compare two different field in set expression?

anindya_manna — Thu, 05 Apr 2018 14:28:23 GMT

Can you please attach your qvw?

Re: How to compare two different field in set expression?

vishsaggi — Thu, 05 Apr 2018 14:37:46 GMT

Check here.

Re: How to compare two different field in set expression?

anindya_manna — Tue, 10 Apr 2018 13:28:21 GMT

avg({$<[Process Date]={">=$(=date(addmonths(CAMPAIGN_START_DATE,-6),'MM/DD/YYYY')) <$(=date(CAMPAIGN_START_DATE,'MM/DD/YYYY'))"}>}[Sales Amt])