topic Re: set analysis by using varaible in App Development

set analysis by using varaible

Mike_Y — Sat, 16 Nov 2024 20:54:47 GMT

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.

Re: set analysis by using varaible

Vegar — Thu, 09 May 2019 06:07:33 GMT

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.

Re: set analysis by using varaible

Mike_Y — Thu, 09 May 2019 06:15:12 GMT

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.

Re: set analysis by using varaible

Vegar — Thu, 09 May 2019 09:20:54 GMT

R10 days

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

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

Re: set analysis by using varaible

Mike_Y — Thu, 09 May 2019 17:02:24 GMT

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

Re: set analysis by using varaible

Vegar — Thu, 09 May 2019 18:09:47 GMT

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.

Re: set analysis by using varaible

Mike_Y — Thu, 09 May 2019 20:48:57 GMT

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.

Re: set analysis by using varaible

Vegar — Thu, 09 May 2019 21:09:07 GMT

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.

Re: set analysis by using varaible

Mike_Y — Thu, 09 May 2019 21:40:31 GMT

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?

Re: set analysis by using varaible

Vegar — Thu, 09 May 2019 21:56:02 GMT

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

Re: set analysis by using varaible

Mike_Y — Fri, 10 May 2019 03:42:12 GMT

When I tried to add the ONLY set analysis result to the straight table, I got the "Invalid dimension" error.

Not sure what happened.

If possible, can you please try it at your end?
Many thanks.

Have a good day.

Re: set analysis by using varaible

Vegar — Fri, 10 May 2019 11:39:40 GMT

Re: set analysis by using varaible

Mike_Y — Fri, 10 May 2019 18:57:40 GMT

Hi Vegar,

Thanks for helping me.

Your example works perfectly.

I learned something new.

Can you explain to me why Dimension cannot work but Measure can, please?

Moreover, my current Qlik Sense Desktop is on version April 2019, but I could not find the calendar bundle you used.

Look forward to hearing from you.

Have a great day.

Re: set analysis by using varaible

Vegar — Sat, 11 May 2019 05:23:06 GMT

The bundle objects are found together with your extensions not together with the standard Qlik Sense objects.

When installing Qlik Sense adding the dashboard and visualization bundles are optimal, check with your Qlik admin if you can not find them in your setup
.

Re: set analysis by using varaible

Mike_Y — Sat, 11 May 2019 06:35:46 GMT

Hi Vegar,

Thanks for your reply.

I understand the bundle package needs to be installed explicitly.

I am now using April 2019 Qlik Sense Desktop. Attached is all the extensions I can see in the bundle package.

Hope to hear from you.

Have a good weekend.

Re: set analysis by using varaible

Vegar — Sat, 11 May 2019 18:36:39 GMT

It is located in the *dashboard bundle *not in the *visualization bundle. *

Check out @ArturoMuñoz blog post
https://community.qlik.com/t5/Qlik-Design-Blog/The-new-Dashboard-bundle/ba-p/1509183

Re: set analysis by using varaible

Mike_Y — Mon, 13 May 2019 01:37:48 GMT

Hi Vegar,

Hope you had a lovely weekend.

Yes, I found it.

I will need to manually import it because I have missed the previous Qlik update.

Have a good day.

Re: set analysis by using varaible

Mike_Y — Mon, 13 May 2019 02:14:22 GMT

Hi Vegar,

If one date has multiple records, the ONLY function refused to work properly.

In this case, can I achieve the same outcome?

Please kindly help me.

Thanks.

Re: set analysis by using varaible

Mike_Y — Mon, 13 May 2019 03:25:41 GMT

Hi Vegar,

Me again.

I am writing to update you that I have solved my problem by applying only and set analysis to a column having a unique ID.

Happy now.