Using nested functions within Set Analysis

[#1]

Hi Everyone,

This might seem simple, but I am quite new to Qlikview.

I have sales data loaded into qlikview on a weekly basis. I.e, every week data is loaded with sales of various clients for that week. I currently have a selection box that lets users select a date and a table that shows sales (and some other information) for each client.

I would like to now display a Year to date number for each client, based on the current selection. Assuming the user clicks on 15/12/2012, then I want a sum of all weeks of sales upto that date. That is, sum ALL weeks (or rows) that are LESSER THAN OR EQUAL TO the currently selected date.

My reasoning was I would need to use a set analysis that uses current selection AND a date comparing function.

My date field that the user selects is called SelectDate and my Sales field is called Sales. I only have 2012 data loaded.

This is what I came up, and it is miserably failing!

Sum ( {1 <SelectDate<={GetFieldSelections(SelectDate)}> } Sales)

Im sure i am tripping up with the bit in red i,e using <= for lesser than or equal to. But even if i remove the "<", my expression does not work.i.e

Sum ( {1 <SelectDate={GetFieldSelections(SelectDate)}> } Sales).. Which I assumed should simply sum all data based on current selection.

Any help will be much appreciated!

[Anonymous · Accepted solution]

Hi!

Quite some ways to achive this:

=sum({$<SelectDate={">=$(=yearstart(SelectDate))<=$(=only(SelectDate))"}>} Sales)

$... respect the current user selection

then overwrite SelectDate with a dynamic expression similar to:

SelectDate={">=01/01/2003<=10/01/2013"}

This tool can help with Set Analysis: http://tools.qlikblog.at/SetAnalysisWizard/

[#3]

Hi rva_heldendaten,

Thank you so much for your reply.

I did paste your formula into the expression and it seems to work!! Ill have to recheck the numbers as I have a lot of data. (Atleast it produces an output, much better than mine..)

Unfortunately though, I am bamboozeled by the logic of the formula. I would be grateful if you could explain it to me so that I can design similar ones in the future.

Here are some of my questions:

1. I assume yearstart() gives the start date of the current year. I.e if the user selects 18/12/2012, yearstart() returns 01/01/2012

2. I am not sure what the only() funtiuon does, but I assume in this case that it returns 18/12/2012

3. What really confuses me, is the $ sign at the start of the formulas. Does that not reduce the formulas to work only on the current selection or rows of data?

Let me explain how I understand the logic, and if you can correct my flaw, it would be great.

Let's assume my universal data set (which captures sales on a weekly basis) is:

SelectDate    Customer Sales

04/12/2012 A 100

04/12/2012 B 200

04/12/2012 C 300

11/12/2012 A 400

11/12/2012 B 500

11/12/2012 C 600

18/12/2012 A 700

18/12/2012 B 800

18/12/2012 C 900

25/12/2012 A 1000

25/12/2012 B 1100

25/12/2012 C 1200

Now, if the user selects 18/12/2012 in the selection, my understanding is that yearstart() returns 1/1/2012 and only() returns 18/12/2012. BUT what confuses is me is that if the formula begins with a $, i.e "=sum({$<SelectDate", Wont the data set that the formulaes then work on be reduced to just the current selection, i.e

18/12/2012 A 700

18/12/2012 B 800

18/12/2012 C 900

Which will then return sales of 2400 instead of the correct answer of 4500

Would be great if you could point out the flaw in my logic!

Thank you!

[Anonymous]

$ respects the current User selection.

So if a user selects "Customer A", my expression will only return YTD for this Customer.

--> my expression says: Respect all Userselections, but overwrite the date selection with =>1/1/2012<=18/12/2012

If you use "1" in set analysis, qlikview will ignore the user's "Customer A" selection, and will return all Sales between "=>1/1/2012<=18/12/2012"

I would say "$" is more often used than "1"ö, as typically the user has done selection in various fields (article, customer, country) and you only want to overwrite the timeselection..

[#5]

Thank you so much for that

Right, Think i can understand it better now. So the formulas withing the curly brackets merely override the SelectDate figures.

Last question - What does the only() function do?

Thanks

[Anonymous]

it ensures that only() ONE date is selected;

If a user has multiple dates selected, it will return NULL.

Otherwise you could use the min() or max() function instead of only(). As long as just one date is selected, it wont make any difference..

[#7]

Hi There,

So a few days ago, I loaded January data into the Qlikview file, and Ive noticed 2 things.

1. The function above seems to capture Year to date figures for 2012 and 2013 when the user clicks on a 2013 week.

2. I also mimiced the above functions and replaced "yearstart" with "quarterstart" but it gives me the exact data as the previous functions, i.e the sum of sales for all weeks in 2012 and 2013.

Is there any way to rectify this? I thought the functions yearstar and quarterstart were supposed to return the earliest date of that year/quarter.

Thank you for all your help and I hope to hear from you soon

[#8]

My Apologies, your expressions work perfectly!

Thank you so much