Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tedalien
Contributor III
Contributor III

'IF' Function in Set Analysis

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
tedalien
Contributor III
Contributor III
Author

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

tedalien
Contributor III
Contributor III
Author

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