Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
apoorvasd
Contributor II

How to pick up last week's data of that particular month in QlikView?

Hello Everyone,

I need help on the below requirement.

I have a date field which has dates of only Fridays of that particular month. So this field will have 4 or 5 date values for a month based on number of Fridays in that month. What I want to achieve is, there are AOP and Actual figures against these dates, and in my straight table I would like to display only the data of last week in that particular month. And for the current month, data should be the latest week data.

For Example: Considering that below is the table in excel sheet, I want to display only data for AOP and Actuals for Date, 25-08-2017 for month of August and for September, I have to show data for Date, 01-09-2017 as this is the latest week in September so far.

DateAOPActuals
04-08-2017755585
11-08-201738954
18-08-2017876766
25-08-201725686
01-09-2017784233
08-09-2017265-
15-09-2017987-

How can I show this data in a Straight table? Any suggestions?

Thank you.

15 Replies
MVP
MVP

Re: How to pick up last week's data of that particular month in QlikView?

Create a Month field in the script. Then try like:

Chart Dimension:  =Aggr( Max(If(Date<=Today(), Date)), Month)

MVP
MVP

Re: How to pick up last week's data of that particular month in QlikView?

The simplest way is to add last Friday flag in the load script like this:

// Load fact table

Fact:

LOAD Date,

  AOP,

  Actuals,

  ...

FROM ...

//Add last Friday flag

Left Join (Fact)

LOAD Max(Date) as Date

  1 as LastFridayFlag

Resident Fact

Group By MonthName(Date);

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
apoorvasd
Contributor II

Re: How to pick up last week's data of that particular month in QlikView?

Hi Jonathan,

Thanks for your suggestion and this is close to what I want. I see that only last date of that particular month is being picked up which is fine, but I also want the following condition to be satisfied, i.e., for current month, data for latest week has to be picked. Here in my case, the straight table has to show data for 01-09-2017 for now, as this is the latest week so far for September. But in QlikView I see data for 29-09-2017 as this is the last week in this month.

apoorvasd
Contributor II

Re: How to pick up last week's data of that particular month in QlikView?

Hi Tresesco,

I tried your suggestion and I am getting partial result. What I mean is, for few dates I do not see AOP values as you can see in the below picture. I am checking what could be the issue. Let me know if you have any idea why?

image.PNG

Thank you.

MVP
MVP

Re: How to pick up last week's data of that particular month in QlikView?

Could you share your sample data?

apoorvasd
Contributor II

Re: How to pick up last week's data of that particular month in QlikView?

Attached is the sample data.

Since AOP figures have same data for all the weeks, I am able to fetch this data in QlikView. But as Actual vary for each week, the data is not seen in the table.

Can you please help with that?

Thank you.

MVP
MVP

Re: How to pick up last week's data of that particular month in QlikView?

>> Here in my case, the straight table has to show data for 01-09-2017 for now, as this is the latest week so far for September.

Add a where clause to limit the Max. Perhaps something like

//Add last Friday flag

Left Join (Fact)

LOAD Max(Date) as Date

  1 as LastFridayFlag

Resident Fact

Group By MonthName(Date)

Where Sum(AOP) > 0;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
apoorvasd
Contributor II

Re: How to pick up last week's data of that particular month in QlikView?

I am getting an unknown error when tried to execute the script with this code!

Unknown error.PNG

MVP
MVP

Re: How to pick up last week's data of that particular month in QlikView?

In 9/1/2017, you don't have data for ACT. What do you expect to see for it, null or the previous available data ?

Community Browser