Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mrutyunjaya
New Contributor II

Automate Set Analysis using Date Fields

Hi,

I have set of Fields;

Year = 2016,2017

HalfYear = FY17-H1, FY17-H2

Quarter = FY17-Q1, FY17-Q2, FY17-Q3, FY17-Q4

And trying to create a straight table using Set Analysis:

Sum({<Region = {'North'}, Year= 2017>}Sales)

Sum({<Region = {'North'}, HalfYear= {'FY17-H1'}>}Sales)

Sum({<Region = {'North'}, Quarter={'FY17-Q4'}>}Sales)


Is it possible to write ONE expression and the values to be changed based on the above selections (Year, HalfYear, Quarter);

Hope we can use Getfieldselections() or Getcurrentselections() etc.

Also, I need to write expressions by comparing two dates:

Ex: (Sum({<Region = {'North'}, Quarter={'FY17-Q4'}>}Sales  -  Sum({<Region = {'North'}, Quarter={'FY16-Q4'}>}Sales)*100

How to automate both expressions, so that the values should change in the same object based on ONE Selection.


Reg,

Mrutyunjaya

Tags (1)
5 Replies
isorinrusu
Contributor III

Re: Automate Set Analysis using Date Fields

Hi Mrutyunjaya,


If you use the field selections, you wouldn't have to use Set Analysis (except for the region).

Luminary
Luminary

Re: Automate Set Analysis using Date Fields

Hi,

I think this blog post might help you: Dates in Set Analysis

Best

Juraj

i_bagnoli
Contributor

Re: Automate Set Analysis using Date Fields

Hi Mrutyunjaya,

I think you can solve your first request just using expression Sum({<Region = {'North'}>}Sales) and then changing selection for Year, HalfYear and Quarter directly in layout.

However, your second request is a YTD analysis and you can build it with the use of variables, for es. vMaxQuarter and vMaxQuarter_m1, which can be populated dinamically with your selections, and replace 'FY17-Q4' and 'FY16-Q4' in set analysis.

I'm used to define a quarter as a number: 20171 (Q1), 20172 (Q2)... That's useful in variables and I can define vMaxQuarter as

year((v_max_date))*10+((month((v_max_date))-if(mod(month((v_max_date)),3)=0,3,mod(month((v_max_date)),3)))/3+1)

and then vMaxQuarter_m1 as vMaxQuarter-10.

Here I suppose I have a date field to select so that v_max_date can be max(datefield) or only(datefield), which changes according with selections. If you have not, you have to determine the 2 variables above through the analysis of the text 'FY17-Q4' selected ...

I hope it helps.

Bye

IB

mrutyunjaya
New Contributor II

Re: Automate Set Analysis using Date Fields

Hi Irene,

Thanks...that is really helpful.

The first part..YES you are right & it works the way you said (I added it for the ease of understanding)

I am actually looking for the second part - where I have created 50+objects for Quarterly views using;

Ex: (Sum({<Region = {'North'}, Quarter={'FY17-Q4'}>}Sales  -  Sum({<Region = {'North'}, Quarter={'FY16-Q4'}>}Sales)*100,

And added $(vCurrentQuarter) & $(vPrevQuarter) wherein I always get the values of 'CurrentQuarter' and 'PrevQuarter' based on today().

However, I am trying to make the same objects dynamic based on Selections (Year, HalfYear, Quarter) Else I will end up creating

50 more objects for Year

50 more objects for Halfyear


Please suggest how can I automate it better.

Reg,

Mrutyunjaya



i_bagnoli
Contributor

Re: Automate Set Analysis using Date Fields

Hi Mrutyunjaya,

I attach a qvf file example with sample data to show you exactly what I mean and what I think could be done.

In the qvf you will find just one sheet: up a list box with Year, YearMonth and Quarter available for direct selection; down 2 tables. The first table has Total Sales column, Today Q. Sales, Today Q. Sales (Y-1), Max Q. Sales and Max Quarter Sales (Y-1).

The last two columns dinamically change with selections.

Then, if you'd like just 2 columns and not 4, you could use getselectioncount as you suggested: you will find my example in the second table. Varying selections on Year, YearMonth or Quarter the Sales values will adapt.

However set analysis is always based on Quarter field: if you want YTD Analysis even on Month or Year base, you have to create different expressions...

One hint, in this situation (but I don't know if this scenario is suitable for you), I prefer reload data till today and so make today coincide with max date, in order to always use "Max" conditions, avoiding the use of getselectioncount or getfieldselection which could cause problems with selections not prevented... 

Hope it helps.

IB

Community Browser