Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello I am new in QlikSense and I need your help 😉
I have Products, Sales and a Calendar on SalesDate
I need a table to show product Name,sku,brand,..., sales on a period, Sales on the same period previous year
The criteria on calendar's fields should not filter my selection ! in fact if I choose year 2016, i want to see in my table products sold in 2015 too
So i have create an alternate state [SelDate] and assign "the Date Selection" to this Alternate State.
I've created a new measure like this : sum({<[Year]={$(=Max([Year])-1)}>} [Sales.Qty]*[Sales.Price]) , it's ok --> if have the amount for 2015 because i have no selection on date, so the max date = 2016 - 1 = 2015.
So i want to to have the max date from my alternate state :
Sum({<[Year]={$(=Max([SelDate]::[Year])-1)}>} [Sales.Qty]*[Sales.Price])
but it's return 0. Syntax problem ?
My final goal it's to create a variable with one argument, like this variable for my alternate state :
$(vClearCalendar), [Year] = {$(=Max([Year])-$1)}, [_DaySerialYear] = {"<=$(=Max([_DaySerialYear])) >=$(=Min([_DaySerialYear]))"}
_DaySerialYear is a field added in the calendar = Month *100 + Day to manage correctly 29th of February.
vClearCalendar is another variable to reset all calendar criteria ='['& Concat(distinct {1<$Table={'Calendrier'}>}$Field, ']=, [')&']='
Someone can help me solve my problem ?
Thanks in advance,
Best regards,
Stephane
Try this for your first issue:
Sum({<[Year]={$(=Max({SelDate} Year)-1)}>} [Sales.Qty]*[Sales.Price])
Can you elaborate a little more for your second issue?
Thanks you Sunny it works !
The second issue wasn't a problem, i have created a variable with this code :
$(vClearCalendar), [Year] = {$(=Max({SelDate} [Année])-$1)}, [_DaySerialYear] = {"<=$(=Max({SelDate}[_DaySerialYear])) >=$(=Min({SelDate}[_DaySerialYear]))"}
This variable take one argument. When i use this variable in a formula, It's calculated the value for the same period of date for a previous year (argument)
Best regards,
Stephane