Filtering dates - Grouping quarters etc

[#1]

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.

Thanks in advance!

Regards

Arkadeep

[webpomer]

Hey,

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

Set Analysis: syntaxes, examples

[Gysbert_Wassenaar]

Try this expression as measure:

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

[morgankejerhag]

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)

[MarcoWedel]

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:

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