14 Replies Latest reply: Oct 1, 2015 3:25 AM by abhay singh

# Set Analysis Help

Hey guys,

i have one exp with the one field i.e.

CurrentTime_Date

i want to define a fromDate and ToDate in My Expression..i cant use Aggregation function since value contain Date and Time..

How can i define it in set Analysis..

{<FromDate = >FromDate and ToDate=    < ToDate>} CurrentTime_Date

Abhay

• ###### Re: Set Analysis Help

Something like this:

Two helper variables:

vFromDate: =YearStart(Today())

vToDate: =Today()

A set analysis expression using the variables:

Sum( {<CurrentTime_Date={'>=\$(vFromDate)<=\$(vToDate)'}>} Amount)

• ###### Re: Set Analysis Help

Hi Gysbert,

Actually the problem is I have only one measure which i can not use with Aggr Functions...

Because Measure is Date_Time_Temperature which contain value like below

30-09-15  at 10:44:01 AM

so i can not use Sum,Count etc.

The reason why i am doing is i need to show FromDate to ToDate Range Data in particular report and as i am working on Qliksense, i Dont have Calender Object or Can not Use variables.

so i have created FromDate and ToDate at backend now i require to pass the expression >FromDate and <ToDate so i can get the range of inpput entered by user.

Abhay

• ###### Re: Set Analysis Help

Hi Abhay,

If you can't use aggregation functions then try with Only() function.

So declare two variables to include in the set analysis expression:

1) vFromDate: =YearStart(Today())

2) vToDate: =Today()

Create the following set analysis expression using the variables:

Only( {<CurrentTime_Date={'>=\$(vFromDate)<=\$(vToDate)'}>} Date_Time_Temperature)

Alvaro P.

• ###### Re: Set Analysis Help

In QS, you can define the variables in script:

Set vFromDate = =YearStart(Today());  // yes that is two = signs with a space

Set vToDate = =Today();

And any QV calendar load script will work in Sense.

• ###### Re: Set Analysis Help

And you need to split CurrentTime_Date into times and dates:

...

CurrentTime_Date,

Date(Floor(CurrentTime_Date)) As CurrentDate,

Time(Frac(CurrentTime_Date)) As CurrentTime,

...

• ###### Re: Set Analysis Help

Hi,

in Qlikview it is something like that
{<[ref date] = {"\$(= '>=' & vNumFrom & '<=' & vNumTo)"}

but I am using variable to define FROM and TO Date.

Cheers,

Patric

• ###### Re: Set Analysis Help

hello,

you have to create a variable with the date FromDate  and ToDate or other form is in the expression with the fields.

if you compare two dates the decimal or frac part of the number isn't important.

-example in the expression:

sum({<Date={">\$(=date(FromDate))<=\$(=date(ToDate))"}> }Value)

sorry for my english i'm from chile

• ###### Re: Set Analysis Help

Abhay,

Re: Is there a way to add Date Picker Calendar object in Qlik Sense?

Hope this help!!

• ###### Re: Set Analysis Help

Chk the attachment for your Min temp/hour...

Hope this will help!!

• ###### Re: Set Analysis Help

For your drilling & Trend you need to take Avg(Temp) & slight change which I have done.

PFA..and to represent better Hour value you can use Class Function in the drilling for SHour which would give you output like..

0-1, 1-2, 2-3, 3-4 instead of 0,1,2,3,4....

• ###### Re: Set Analysis Help

You can use this expression in drilling for SHour....

=SubField(Class(SHour,1),'<',1)&'-'&SubField(Class(SHour,1),'<',3)

Which would give you result like....10-11,11-12,12-13 etc

• ###### Re: Set Analysis Help

Replace Avg(Temp) with Min(Temp)......

Its working..

• ###### Re: Set Analysis Help

How to Do sorting in it??

0-1, 1-2, 2-3,3,3-4 and so on

• ###### Re: Set Analysis Help

Sorting Done In QlikSense