topic Re: Default Select Values in QlikView

Default Select Values

Thu, 15 Sep 2011 13:30:19 GMT

hi there - im relatively new to qlikview and wanted to know if it was possible to have data in a straight table filter by a date set in a slider... my report has got columns for Year2Date, Month2Date and AllDates. So if the user selects the date on slider to be 2011/07/25, then the full report will update according to the formula:

AllDates: SUM(SalesAmount)

Month2Date: SUM(IF(YEAR = Year(vDateOnSlider), IF(MONTH = Month(vDateOnSlider), SalesAmount)))

Year2Date: SUM(IF(YEAR = Year(vDateOnSlider), SalesAmount))

What i found was that if I dont have the YEAR and MONTH as 'Select Fields' on my screen, the data does not show on my table...

I hope this makes sense.

Thanks

Default Select Values

Thu, 15 Sep 2011 13:36:59 GMT

Hi,

try with this set analysis

MTD = Sum ({$<#_Date_NUM_KEY = {"<=$(=Max(#_Date_NUM_KEY))"},Month_Num = {'$(=Num(Month((Max(Date)))))'},Year = {'$(=Num(Year((Max(Date)))))'},Date=>}M_STORE_SALES_AMT_CY)

YTD = Sum ({$<#_Date_NUM_KEY = {"<=$(=Max(#_Date_NUM_KEY))"},Year = {'$(=Num(Year((Max(Date)))))'},Date=>}M_STORE_SALES_AMT_CY))

For these Expression you need to create a Date in Number Format using this Functaion "Num(DateField)" and Month also in Number format "Num(Month(DateField))"

Re: Default Select Values

Fri, 16 Sep 2011 05:54:33 GMT

thanks for the reply santhugsk, but this is quite advanced. Would you be able to assist with an example using my variables below?

What I have noticed is the slider is actaully a value i convert into a date later on... not sure if that helps?

Year the sale was made in = 'YEAR'

Sales Amount = 'SALES'

Number from Slider = 'vReportDateSlider'
Date(vReportDateSlider) = 'vReportDate'

My calculation is as follows:

IF(YEAR = Year($(vReportDate)),sum(Amount))

Re: Default Select Values

Sat, 17 Sep 2011 14:49:01 GMT

Hi,

try this little example.

I hope this help you.

C u,

Stefano.

Re: Default Select Values

Sun, 18 Sep 2011 17:04:37 GMT

which example Ste.San?

I tried this (but returned nothing):

sum( {1<YEAR= { vReportDate} >} SALES)

The code looked like it didnt 'like' vReportDate... I changed it to 2011 and that worked... but i cant define it as 2011, it has to be selectable

Default Select Values

Mon, 19 Sep 2011 05:47:40 GMT

Ok, I found this one forum that guided me, but still never came right:

It accepts the following code, but returns nothing:

Sum({< YEAR = {$(vReportDateYear)} >} SALES)

(i declared a variable "vReportDateYear" which equals "Year(vReportDate)")

Would this have anything to do with what santhugsk mentioned above:

For these Expression you need to create a Date in Number Format using this Functaion "Num(DateField)" and Month also in Number format "Num(Month(DateField))"

Default Select Values

Mon, 19 Sep 2011 06:14:01 GMT

Ok - I came right:

Sum({< YEAR = {$(vReportDateYear)} >} SALES)

But I changed my variable to "=Year(ReportDate)" (the equals sign was needed before the "YEAR...")

Is it possible to include an "AND" clause? I.E. to say i want SALES for YEAR = vReportDateYear AND MONTH = vReportDateMonth ?

thanks for the helpo

Re: Default Select Values

Mon, 19 Sep 2011 06:34:45 GMT

got it:

Sum({< YEAR = {$(vReportDateYear)} , MONTH = {$(vReportDateMonth)} >} SALES)

Re: Default Select Values

Mon, 19 Sep 2011 07:07:01 GMT

Sorry,

i've forgotten the attach...