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: 
aetingu12
Creator
Creator

Set Analysis in expression when one date equals another

All,

This should be a simple task, but for some reason I might be over complicating it.  I have a Summary_Date column, a Account_Open_Date and Balances.  I want the pivot table to just show the Balances where the Account_Open_Date equals the Summary_Date.  Here is a sample:

LOAD * INLINE [

    Summary Date, Account ID, Customer, Account Open Date, Balances

    2018-01-31, 1, A, 2018-01-31, 100

    2018-02-28, 1, A, 2018-01-31, 200

    2018-03-31, 1, A, 2018-01-31, 300

    2018-04-30, 1, A, 2018-01-31, 400

    2018-05-31, 1, A, 2018-01-31, 500

    2018-06-30, 1, A, 2018-01-31, 600

    2018-04-30, 2, B, 2018-04-30, 100

    2018-05-31, 2, B, 2018-04-30, 200

    2018-06-30, 2, B, 2018-04-30, 300

    2017-01-31, 3, C, 2017-01-31, 100

    2017-02-28, 3, C, 2017-01-31, 200

    2017-03-31, 3, C, 2017-01-31, 300

    2017-04-30, 3, C, 2017-01-31, 400

    2017-05-31, 3, C, 2017-01-31, 500

    2017-06-30, 3, C, 2017-01-31, 600

    2017-07-31, 3, C, 2017-01-31, 700

    2017-08-31, 3, C, 2017-01-31, 800

    2017-09-30, 3, C, 2017-01-31, 900

    2017-10-31, 3, C, 2017-01-31, 1000

    2017-11-30, 3, C, 2017-01-31, 1100

    2017-12-31, 3, C, 2017-01-31, 1200

    2018-01-31, 3, C, 2017-01-31, 1300

    2018-02-28, 3, C, 2017-01-31, 1400

    2018-03-31, 3, C, 2017-01-31, 1500

    2018-04-30, 3, C, 2017-01-31, 1600

    2018-05-31, 3, C, 2017-01-31, 1700

    2018-06-30, 3, C, 2017-01-31, 1800

];

Screen Shot 2018-07-09 at 19.02.04.png

So I would this to be only 100 for 2018-01-31 and 100 fir 2018-04-30.

Your help is more than appreciated.

Regards,

Aksel

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

are you looking for this?

Capture.PNG

If Yes,

try like below

Dates:

LOAD *, Floor(Date#(SummaryDate, 'YYYY-MM-DD')) AS SumDtID, Floor(Date#(AccountOpenDate, 'YYYY-MM-DD')) AS AcctDtID INLINE [

    SummaryDate, AccountID, Customer, AccountOpenDate, Balances

    2018-01-31, 1, A, 2018-01-31, 100

    2018-02-28, 1, A, 2018-01-31, 200

    2018-03-31, 1, A, 2018-01-31, 300

    2018-04-30, 1, A, 2018-01-31, 400

    2018-05-31, 1, A, 2018-01-31, 500

    2018-06-30, 1, A, 2018-01-31, 600

    2018-04-30, 2, B, 2018-04-30, 100

    2018-05-31, 2, B, 2018-04-30, 200

    2018-06-30, 2, B, 2018-04-30, 300

    2017-01-31, 3, C, 2017-01-31, 100

    2017-02-28, 3, C, 2017-01-31, 200

    2017-03-31, 3, C, 2017-01-31, 300

    2017-04-30, 3, C, 2017-01-31, 400

    2017-05-31, 3, C, 2017-01-31, 500

    2017-06-30, 3, C, 2017-01-31, 600

    2017-07-31, 3, C, 2017-01-31, 700

    2017-08-31, 3, C, 2017-01-31, 800

    2017-09-30, 3, C, 2017-01-31, 900

    2017-10-31, 3, C, 2017-01-31, 1000

    2017-11-30, 3, C, 2017-01-31, 1100

    2017-12-31, 3, C, 2017-01-31, 1200

    2018-01-31, 3, C, 2017-01-31, 1300

    2018-02-28, 3, C, 2017-01-31, 1400

    2018-03-31, 3, C, 2017-01-31, 1500

    2018-04-30, 3, C, 2017-01-31, 1600

    2018-05-31, 3, C, 2017-01-31, 1700

    2018-06-30, 3, C, 2017-01-31, 1800

];

Then in Pivot add Dim

Summary date and Account open date

Expr:

= Sum(IF(AcctDtID = SumDtID, Balances))

View solution in original post

4 Replies
vishsaggi
Champion III
Champion III

are you looking for this?

Capture.PNG

If Yes,

try like below

Dates:

LOAD *, Floor(Date#(SummaryDate, 'YYYY-MM-DD')) AS SumDtID, Floor(Date#(AccountOpenDate, 'YYYY-MM-DD')) AS AcctDtID INLINE [

    SummaryDate, AccountID, Customer, AccountOpenDate, Balances

    2018-01-31, 1, A, 2018-01-31, 100

    2018-02-28, 1, A, 2018-01-31, 200

    2018-03-31, 1, A, 2018-01-31, 300

    2018-04-30, 1, A, 2018-01-31, 400

    2018-05-31, 1, A, 2018-01-31, 500

    2018-06-30, 1, A, 2018-01-31, 600

    2018-04-30, 2, B, 2018-04-30, 100

    2018-05-31, 2, B, 2018-04-30, 200

    2018-06-30, 2, B, 2018-04-30, 300

    2017-01-31, 3, C, 2017-01-31, 100

    2017-02-28, 3, C, 2017-01-31, 200

    2017-03-31, 3, C, 2017-01-31, 300

    2017-04-30, 3, C, 2017-01-31, 400

    2017-05-31, 3, C, 2017-01-31, 500

    2017-06-30, 3, C, 2017-01-31, 600

    2017-07-31, 3, C, 2017-01-31, 700

    2017-08-31, 3, C, 2017-01-31, 800

    2017-09-30, 3, C, 2017-01-31, 900

    2017-10-31, 3, C, 2017-01-31, 1000

    2017-11-30, 3, C, 2017-01-31, 1100

    2017-12-31, 3, C, 2017-01-31, 1200

    2018-01-31, 3, C, 2017-01-31, 1300

    2018-02-28, 3, C, 2017-01-31, 1400

    2018-03-31, 3, C, 2017-01-31, 1500

    2018-04-30, 3, C, 2017-01-31, 1600

    2018-05-31, 3, C, 2017-01-31, 1700

    2018-06-30, 3, C, 2017-01-31, 1800

];

Then in Pivot add Dim

Summary date and Account open date

Expr:

= Sum(IF(AcctDtID = SumDtID, Balances))

View solution in original post

aetingu12
Creator
Creator

Yes, but I just want to restrict to those days.  So don't want to see the other dates.  But then your code gave me my jump start my brain needed:)

I just needed to do the last bit.

sum(if([Summary Date] = [Account Open Date],Balances))

That gave me what I was looking for!

Thanks again,

Aksel

vishsaggi
Champion III
Champion III

In presentation tab of your Pivot chart properties, supress zero values.

I used the DateID's coz it is always a better practice to use Integer comparisons rather than string values hence created two DateIDs.

aetingu12
Creator
Creator

How do I check if the field I use is String or Integer?  Or do you just convert it to string by using DATE# regardless.

The reason I ask is because, when I have dates that are not monthends I seem to be running in to some issues.  So I use;

Date#(MonthEnd(SUMMARY_DATE,'YYYY-MM-DD')) which is not bringing in any data.