Suggest Set Analysis for 'IF Statement'

[#1]

Hi All

Can some one help me out to write the Set Analysis for the below three IF Statement as the performance of the chart is poor because of this. If not for all the 3 whatever is known please let me know that atleast.

 SUM(IF(StartDate<=date AND EndDate>=date,1))

and

if(only(weekday(date))='Sat' or only(weekday(date))='Sun',darkgray(),
IF(A1='Change1' ,lightgray(),
IF(A1='Change2',lightred(),
IF(A1='Change3',yellow(),
IF(A1='Change4',lightmagenta(),
IF(A1='Change5',lightcyan(),
IF(A1='Change6',lightblue(),
green())))))))

and also for this

=if(weekday(date) ='Sat' or weekday(date) ='Sun',darkgray())

[Miguel_Angel_Baeyens]

Hello,

Your first expression, assuming StartDate and EndDate are fields and "date" a variable should work as

SUM({< StartDate = {'<=$(=Date(date))'}, EndDate {'>=$(=Date(date))'} >} 1)

The second one is a pure conditional not an aggregation, so it won't work with set analysis. Rather I'd move that condition to the script, and take the value of that field in the color condition. Provided you have a calendar table linked to your fact table, a mapping table should do the trick. And the same for the third, you can always get your weekday in the script, assign the color as value of the field and use it in the chart.

Hope that helps.

[#3]

Miguel Angel Baeyens wrote:
Your first expression, assuming StartDate and EndDate are fields and "date" a variable should work as <blockquote>SUM({< StartDate = {'<=$(=Date(date))'}, EndDate {'>=$(=Date(date))'} >} 1) </blockquote><div></div>

I tried the above one but it is not working as expected. May be becuase of the wrong assumption! As per your assumption "date" is not a variable it is field. Can you please do the required changes in the above set analysis.

[Miguel_Angel_Baeyens]

Hi,

SUM({< date = {'>=$(=Date(EndDate))<=$(=Date(StartDate))'} >} 1)

Hope that helps

[#5]

Miguel Angel Baeyens wrote:
SUM({< date = {'>=$(=Date(EndDate))<=$(=Date(StartDate))'} >} 1) <pre>

Sorry! Above Set Analysis is not working as expected. The reason why I was trying to replace the 'IF Statement' was to improve the performance. But unfortunately after using the Set Analysis performance is much poor and also it is not working as expected.

Thanks for your effort and time. If you get to know any other solution please do let me know.

[Miguel_Angel_Baeyens]

Hi,

It's difficult to me to understand how set analysis performs poorer than an If() expression, anyway not knowing the datamodel, I think that everything is possible.

But if it's not working as expected, there's likely an issue with date formatting or data modelling, because the expression above is the tipical example when selecting a date between a range of dates.

I don't dare to go any further without a better understanding of your document, the data model and so.

Regards.

[#7]

Hi Miguel

I was also bit surprised to see the performance of chart after using the Set Analysis. I also agree with that it is not easy to proceed further with knowing the date formatting and data modelling.

Consider the application which is there in the below link as sample application. This is sample of the application which I am working on.

http://community.qlik.com/forums/t/44440.aspx

Hope you can go further now! If you get any solution please do let me know.

[#8]

Hi Miguel

To know the reason why it is not working. Requesting you to use your Set Analysis in the expression of the chart. You will be see the difference in chart and also you will see the performance.

If you have any doubt please do let me know.

[Miguel_Angel_Baeyens]

Hi,

Yep, both tables are isolated, so any calculation will take a lot of time since it needs to compare very value in table "Calendar" for every value in table "Data". You should associate both tables through a common field. Since you have a calendar that calendar is likely to work on some of the dates you have (StartDate, EndDate -that, by the way, are not variables, but fields, that was misleading me). Besides, your calendar seems to have lots of repeated values (16K+ rows) which is likely an error (probably some unwanted concatenation or joining).

Hope that helps.

[#10]

Hi Miguel

Still I couldn't proceed further. If you don't mind can you please give me some other suggestion to proceed further. Imagine this is how the data is. Also I don't the logic behind the 'IF Statement'! I got the code some where from the forum and it is working as expected. So I don't know what changes are required.

Hope you can understand!