topic Re: 'IF' Function in Set Analysis in QlikView

'IF' Function in Set Analysis

tedalien — Fri, 12 Apr 2013 17:27:29 GMT

Hello everyone of the community,

I started since few months using Qlikview as developer and even if I am reading a bunch of books and articles I see that there is a long learning path in front of me. However I am fascinated by Qlikview and I am glad that there is a forum like this full of enthusiastic users.

I have a conceptual question regarding set analysis and the function IF.

I tried an expresssion like the one below but the syntax seems not well taken and it produces no results ('-').

Expression:

sum ({1<

if ([Order]='Billed',[Billing Date],[Order Date])={"$(=date([ReportingDate],'YYYYMM'))"}

>}IF([Unit]='MT',[Order Quantity]*1000,[Order Quantity]))

My question is a general one:

Can I use nested IF in a Set Analysis?

Apologies if I missed a past reply on this topic but I did not find it on the search.

In this case how would much appreciate if you can re-direct me to the appropriate thread!

Many Thanks for your support,

Alen

Re: 'IF' Function in Set Analysis

Gysbert_Wassenaar — Sat, 13 Apr 2013 07:59:34 GMT

What you're trying isn't going to work because the set is calculated first and then the dimensions and expressions of the chart are applied to the calculated set. The set is calculated once for the chart, not per row. So the if statement in the set modifier isn't going to work. If ReportingDate is a field then comparing it with either Billing Date of Order Date won't do what you expect either.

Re: 'IF' Function in Set Analysis

kaushiknsolanki — Sat, 13 Apr 2013 08:03:55 GMT

Hi,

Yes you can use if statement like shown below.

sum({<[$(=if(Status='A',Date1,Date2))] = {"<=$(=makedate(2012,10,01))"}>}Sales)

But for this there should be a selection in the Field Status.

If you dont select the status it will not work, but still you can use few more functions to avoid selection too.

Regards,

Kaushik Solanki

Re: 'IF' Function in Set Analysis

tedalien — Sun, 14 Apr 2013 21:37:23 GMT

Thank you very much for your reply Gysbert,

this comment gave me the idea of an alternative path to follow.

I have added a new dimension Date as calculated by the IF function

directly to the Fact table. Then I guess I will be able to use it in the set analysis but

I could not try it all yet. I'll try tomorrow the rest in the office.

Even if I have a pretty huge application I think is

a good way to go and when all will work I will try reducing the size of it.

Thank again!

Alen

Re: 'IF' Function in Set Analysis

tedalien — Sun, 14 Apr 2013 21:40:47 GMT

Hi Kaushik,

thank you for your Reply. I m not sure I fully understand what you mean.

When I try this the syntax automaitcally consider the "$(=if(Status" as Variable.

Is this what you mean to use a Variable?

For the moment I am testing how it comes adding a dimension to my fact table with the field already

calculated at lines level. Not really a "saving Kbit" solution but I think is worth to check it.

Best Regards,

Alen