Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
apoorvasd
Creator II
Creator 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
tresesco
MVP
MVP

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

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

jonathandienst
Partner - Champion III
Partner - Champion III

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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.

tresesco
MVP
MVP

Could you share your sample data?

apoorvasd
Creator II
Creator II
Author

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.

jonathandienst
Partner - Champion III
Partner - Champion III

>> 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
Creator II
Creator II
Author

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

Unknown error.PNG

tresesco
MVP
MVP

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 ?