Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

agrimroquette
Contributor II

Compare.

Hi all,

I have some topics which has Actual Start , Actual End dates and Planned start and planned End dates,

I want to check the status of topics quarterly, whether the topic is started or not,

that means:-

I have to select current quarter(Oct. Nov. Dec.) from Planned Start and then I have to compare it with Actual Start For which I have to select from today date to 31st Dec or blanks.

   

TopicP. StartA. Start
a1-Dec-201722-Dec-2017
b23-Nov-201724-Nov-2017
c11-Oct-201721-Oct-2017
d14-Jan-201817-Jan-2018
e12-Feb-201822-Feb-2017
f11-Nov-201711-Nov-2017
g10-Oct-201713-Oct-2017
h1-Jan-20182-Feb-2018
i2-Feb-20172-Feb-2017
j23-Mar-201724-Mar-2017
k22-Apr-201824-Mar-2017
l17-Oct-201729-Nov-2017
m11-Dec-201730-Nov-2017
n12-Feb-201714-Feb-2017
o22-Dec-201723-Dec-2017
p21-Nov-201730-Nov-2017

Desired Result- 5

   

TopicP. StartA. Start
a1-Dec-201722-Dec-2017
l17-Oct-201729-Nov-2017
m11-Dec-201730-Nov-2017
o22-Dec-201723-Dec-2017
p21-Nov-201730-Nov-2017

please help how can i achieve this, i have made and calendar for all date fields.

1 Solution

Accepted Solutions

Re: Compare.

Don't know what the logic for End is, but for Start, do you want to see AI, E, & F? Check attached

26 Replies

Re: Compare.

May be this

Table:

LOAD * INLINE [

    Topic, P. Start, A. Start

    a, 1-Dec-2017, 22-Dec-2017

    b, 23-Nov-2017, 24-Nov-2017

    c, 11-Oct-2017, 21-Oct-2017

    d, 14-Jan-2018, 17-Jan-2018

    e, 12-Feb-2018, 22-Feb-2017

    f, 11-Nov-2017, 11-Nov-2017

    g, 10-Oct-2017, 13-Oct-2017

    h, 1-Jan-2018, 2-Feb-2018

    i, 2-Feb-2017, 2-Feb-2017

    j, 23-Mar-2017, 24-Mar-2017

    k, 22-Apr-2018, 24-Mar-2017

    l, 17-Oct-2017, 29-Nov-2017

    m, 11-Dec-2017, 30-Nov-2017

    n, 12-Feb-2017, 14-Feb-2017

    o, 22-Dec-2017, 23-Dec-2017

    p, 21-Nov-2017, 30-Nov-2017

]

Where [P. Start] >= QuarterStart(Today()) and [P. Start] <= QuarterEnd(Today()) and [A. Start] >= Today() and [A. Start] <= QuarterEnd(Today());

agrimroquette
Contributor II

Re: Compare.

Hi, Sunny welcome back

Sunny, i want to show the name of topics in am pivot table, and i am using qlik sense, how can i list down these topics?

Thanks

Re: Compare.

You listed this as a scripting question... didn't you need this in the script? Also, this is posted in QlikView, but is this for Qlik Sense?

Re: Compare.

Create a flag like this

Table:

LOAD *,

If([P. Start] >= QuarterStart(Today()) and [P. Start] <= QuarterEnd(Today()) and [A. Start] >= Today() and [A. Start] <= QuarterEnd(Today()), 1, 0) as Flag;

LOAD * INLINE [

    Topic, P. Start, A. Start

    a, 1-Dec-2017, 22-Dec-2017

    b, 23-Nov-2017, 24-Nov-2017

    c, 11-Oct-2017, 21-Oct-2017

    d, 14-Jan-2018, 17-Jan-2018

    e, 12-Feb-2018, 22-Feb-2017

    f, 11-Nov-2017, 11-Nov-2017

    g, 10-Oct-2017, 13-Oct-2017

    h, 1-Jan-2018, 2-Feb-2018

    i, 2-Feb-2017, 2-Feb-2017

    j, 23-Mar-2017, 24-Mar-2017

    k, 22-Apr-2018, 24-Mar-2017

    l, 17-Oct-2017, 29-Nov-2017

    m, 11-Dec-2017, 30-Nov-2017

    n, 12-Feb-2017, 14-Feb-2017

    o, 22-Dec-2017, 23-Dec-2017

    p, 21-Nov-2017, 30-Nov-2017

];

and then try this for filter pane

=Aggr(Only({<Flag = {'1'}>} Topic), Topic)

agrimroquette
Contributor II

Re: Compare.

ya thats my mistake to put discussion in qlik view and scripting,i apologize,

i want the list of the topics in the app.


regards,

agrimroquette
Contributor II

Re: Compare.

Hi Sunny,

i have made flag in my script,but i am confuse how to use ur made formula to get the list of topics.

can u see my app please,

Thanks

Re: Compare.

Don't see the Flag that I created, can you share the updated app?

agrimroquette
Contributor II

Re: Compare.

i have made flag for the comparison of start vs end, as i have two date fields, i didnt understand how can i attach your made logic into my script

Re: Compare.

What if you add it to this table

Capture.PNG

Community Browser