topic Filtering dates - Grouping quarters etc in QlikView

Filtering dates - Grouping quarters etc

Sat, 15 Nov 2014 09:12:08 GMT

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

Re: Filtering dates - Grouping quarters etc

webpomer — Sat, 15 Nov 2014 10:11:23 GMT

Hey,

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

Set Analysis: syntaxes, examples

Re: Filtering dates - Grouping quarters etc

Gysbert_Wassenaar — Sat, 15 Nov 2014 10:53:02 GMT

Try this expression as measure:

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

Re: Filtering dates - Grouping quarters etc

morgankejerhag — Sat, 15 Nov 2014 18:22:37 GMT

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

MarcoWedel — Sun, 16 Nov 2014 00:33:22 GMT

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