Time variable in setAnalysis (Qlik Sense)

[#1]

Hi all,

I got the following situation:

I'm trying to set a time variable in the set analysis. To do so this is what I've done so far:

1. Created a variable named: vB. This is: =AddYears(Max([FirstRegisterDate.autoCalendar.Year]), -1)

2. This variable returns a numeric value: 42005 (in the automatic formatting)

3. Then in the kpi's set analysis I typed:

              Count({$<[FirstRegisterDate.autoCalendar.Year]] ={'$(=vB)'}>} [uniquecustomerid.CustomerBrand])

And this doesn't work.

Thanks for the help!

[brian_holborow]

Alejandro

if I understand what you are trying to do correctly, you need to add another year function around your expression for the vB variable.  If you check the numeric value 42005 you will see that this is the date 1 Jan 2015, whereas I am guessing you need a value of 2015 - the actual year in question not the date.

Hope that helps.

Please mark this as helpful / correct if your question has been answered to your satisfaction

Brian

[Anonymous]

To clarify a bit...

Set Analysis modifiers are sensitive to the date format.  To make it work, make sure that the right site of the equation has the same format as the field on the left side.  It could be done by using formatting function either in variable, or directly in set expression.

[rupamjyotidas · Accepted solution]

More of this

Let vB =Year(AddYears(Max([FirstRegisterDate.autoCalendar.Year]), -1))

[akash3191]

Hi Alejandro,

Please use below expression for the variable. Make sure you don't forget to put '=' sign in the variable expression.

=Year(AddYears(Max([FirstRegisterDate.autoCalendar.Year]), -1))

Also, you need to modify the set analysis in the KPI expression. Below is the updated one.

Count({$<[FirstRegisterDate.autoCalendar.Year]] ={'$(vB)'}>} [uniquecustomerid.CustomerBrand])

Let me know if this helps.

Thanks,

Akash.

[sasiparupudi1]

set your variable as  Max([FirstRegisterDate.autoCalendar.Year])-1

[#7]

Great!

This worked!

Thank you very much!

[#8]

Hi guys,

Don't know what happened but it doesn't work any longer.

I repeat all the steps taken to see if you can help me out:

1. The original dimension was: FirstRegisterDate (06/10/2004 13:03:25)

2. To set the date, on this dimension I set "DD/MM/YYYY"

3. In the script: Timestamp([FirstRegisterDate] ,'DD/MM/YYYY') as [FirstRegisterDate]

4. Created a variable: vB = Year(AddYears(Max([FirstRegisterDate.autoCalendar.Year]), -1))

5.  My kpi:

count({$<[FirstRegisterDate.autoCalendar.Year]= {$(vB)}>} [UniqueCustomerId])

OR

count({$<[FirstRegisterDate.autoCalendar.Year]= {'$(vB)'}>} [UniqueCustomerId])

Any of these doesn't work.

Hope you can help me

Thank you

[#9]

Just resolved!

Finally, we  had to add a new calculated dimension. This was just the same as FirstRegisterDate (called FirstRegisterDate_AUX)

Then, put in our variable:

vB= Year(AddYears(Max(FirstRegisterDate_AUX), -1))

The other kpis/graphs are set like:

Count({$<[FirstRegisterDate.autoCalendar.Year]= {$(vB)}>} UniqueCustomerId)

Thanks everyone for your help!

Regards