Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
anindya_manna
Partner
Partner

How to compare two different field in set expression?

Hello Experts,

I have a table like below.

   

Process DateCAMPAIGN_START_DATESalesAgent ID
1/1/20182/1/2015 12:00:00 AM500111
1/2/20185/1/2015 12:00:00 AM600222
1/3/20185/13/2015 12:00:00 AM700333
1/4/20185/21/2015 12:00:00 AM800444
1/5/20185/29/2015 12:00:00 AM900555
1/6/20186/2/2015 12:00:00 AM1000666
1/7/20186/3/2015 12:00:00 AM1100777
1/8/20186/9/2015 12:00:00 AM1200888
1/9/20186/18/2015 12:00:00 AM1300999

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

1 Solution

Accepted Solutions
anindya_manna
Partner
Partner

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

View solution in original post

10 Replies
HarishG
Partner
Partner

Hi,

Can you explain more on your actual requirement?

olivierrobin
Specialist III
Specialist III

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

sunny_talwar

What exactly are you trying to get and what object?

anindya_manna
Partner
Partner

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

anindya_manna
Partner
Partner

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


vishsaggi
Champion III
Champion III

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.

sasiparupudi1
Master III
Master III

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)

anindya_manna
Partner
Partner

Can you please attach your qvw?

vishsaggi
Champion III
Champion III

Check here.