set analysis by using varaible

[Mike_Y]

Hi talents,

Can you please help with my set analysis solution?

I have two different variables $(WeekStartDate) and $(tenthWeekEnd).

I set the WeekStartDate variable as the Min() value of bookingKey which contains a lot of dates.

Ideally, I want to pull out all the information between these two dates based on the date I chose.

For example, if I choose 01/05/2019, I should get data from 01/05/2019 to (01/05/2019 + 10 weeks).

My two variables work fine but when I picked 01/05/2019 from the filter, I only got data of 01/05/2019.

How can I improve?

Please help me.

Thanks.

[Vegar]

What do you mean by filter? Selection in a field? You might want to add a cancellation modifier for that field if it is another field than bookingKey.

[Mike_Y]

Hi Vegar,

Thanks for your help.

Yes, by saying filter I meant selection.

I have a bookingKey selection which contains all the dates I need.

I hope to pick one date from the selection and give me data from the picked date to 10 weeks afterwards.

Hope my explanation makes sense to you.

Thanks again.

[Vegar]

R10 days

Assuming you want to calculate Sum(Amount) for the 10 days, try this expression: 

=Sum({<BookingKey = {">$(=max(BookingKey)-10)<=$(=max(BookingKey))"}>} Amount)

[Mike_Y]

Hi Vegar,

Your example looks perfect to me. That is something I was after.

However, I do not need to encode data into a bar chart.

I just need to display all the underlying data in a table which means I do not need to aggregate the data be using sum, count and so on.

Can you show me how to achieve that (without sum function but with set analysis), please?
Moreover, I like the calendar tool you implemented.

Is it a new chart of Qlik? I do not have it in my desktop version.

Many many thanks.

Look forward to hearing from you.

Mike 

[Vegar]

The calendar object is a part of the dashboard bundle that came with the November 2018 release.

In order to use SET analysis you do need to use an aggregating function such as Sum, Max, Min, Avg, and Count, but you can also use only.

Only({<BookingKey = {">$(=max(BookingKey)-10)<=$(=max(BookingKey))"}>} BookingKey)

will display the row date as long you only have one BookingKey associated per row.

[Mike_Y]

Hi Vegar,

Thanks for your further followup.

I tried your solution it gave me an error "Invalid dimension".

Also, I put your query into a KPI chart but nothing popped up just blank.

Any idea?

Thanks again.

[Vegar]

You can not put the only() in a kpi object as it will return more than one date. Your should put it as a measure in your table.

[Mike_Y]

Yes, you're right. It will return multiple rows.

If the only aggregation return can only be regarded as a measure, I guess I will not be able to add it into a table object.

Because a table object can only be based on dimensions.

Am I correct?

[Vegar]

No, you can choose between dimension or measure when adding a new column to
a straight table.