Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Filtering dates - Grouping quarters etc

Hello all.

I am an absolute beginner, so I am apologizing beforehand if my question sounds stupid or for that matter, a similar question has already been answered on the forum.

So, I am working on some user level transaction data, which has fields like customer ID, product ID, sales, purchase date etc.

This is the master data set. Now, for my analysis and presentation, I want to specifically work on the last 4 months of the data set. Overall, the purchase dates in the data set ranges for about 20 months.

I am attaching the necessary date of purchase file for your viewing. I would have uploaded the master data set, but it is unfortunately too large.

As given in this screenshot below, I want to have a filter which doesn't force me to select individual dates, but 4 months together without having to actually scroll and select.

qlik1.PNG.png

Thanks in advance!

Regards

Arkadeep

4 Replies
Partner
Partner

Re: Filtering dates - Grouping quarters etc

Hey,

you have to use set analysis. Here is a very helpful document to learn set analysis:

Set Analysis: syntaxes, examples

MVP & Luminary
MVP & Luminary

Re: Filtering dates - Grouping quarters etc

Try this expression as measure:

sum( {<[DateOfPurchase]={'>$(=addmonths(max([DateOfPurchase])),-4)<=$(=max([DateOfPurchase]))'}>} [NetSales])


talk is cheap, supply exceeds demand
Partner
Partner

Re: Filtering dates - Grouping quarters etc

To simplify this you could also make a flag in the script that shows the four last months. In this case your expression gets simplier.

In the script (in the table with DateOfPurchase):

if(DateOfPurchase >= addmonths(today(),-4),1,0) as Flag_Last4Months

In the expression:

sum({<Flag_Last4Months={1}>} NetSales)

Re: Filtering dates - Grouping quarters etc

Hi,

one solution could be to generate a Month field during load and to show it in descending order, i.e. the last 4 months will be visible and selectable without scrolling:

QlikCommunity_Thread_142276_Pic1.JPG.jpg

LOAD DateofPurchase,

    Year(DateofPurchase) as Year,

    WeekYear(DateofPurchase) as WeekYear,

    'Q'&Ceil(Month(DateofPurchase)/3) as Quarter,

    QuarterName(DateofPurchase) as QuarterName,

    Month(DateofPurchase) as Month,

    MonthName(DateofPurchase) as MonthName,

    Week(DateofPurchase) as Week,

    WeekName(DateofPurchase) as WeekName,

    Day(DateofPurchase) as Day,

    WeekDay(DateofPurchase) as WeekDay

FROM [lib://QlikCommunity_Thread_142276_DateofPurchase.xlsx]

(ooxml, embedded labels, table is Sheet1);

hope this helps

regards

Marco