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: 
Not applicable

Another Set Analysis Q..

Hi

I am very new to qlikview and looking for some help writing a following expression using set analysis. Please pardon me if several question like such are already being posted, I tried my best to use the knowledge from all I could find but still could not resolve on my own. Thanks

To put it in a simple way, I have a list box and a line graph in sheet. Currently, if there is no selection, following is the expression that calculates the line graph.

Sum(if(RET_NAME = 'ABC' AND  (PART_SK ='1' or PART_SK ='2'  or PART_SK = '7'  or PART_SK ='6' or PART_SK ='0' or PART_SK ='5' or  PART_SK ='90' or  PART_SK ='79') AND (WEEK_END_DATE > Date(WeekEnd(Today(),-1)-70)) AND WEEK_END_DATE < Date(WeekEnd(Today(),-1)),PART_COUNT))+Sum(if(RET_NAME = 'ABC' AND PRICE >0 AND (WEEK_END_DATE > Date(WeekEnd(Today(),-1)-70))

AND WEEK_END_DATE < Date(WeekEnd(Today(),-1)),PART_B_COUNT))

Now, the scenario needs to be, regardless of the selection in the list box, I should be able to see what I was seeing before in the Line Graph.

Here is what I tried, but did not work.

Sum({1<RET ={'ABC'},PART_SK  = {1,2,7,6,0,5,90,79}, WEEK_END_DATE = {"=$(>Date(WeekEnd(Today(),-1)-70) < Date(WeekEnd(Today(),-1)))"}>} PART_COUNT ) + Sum({1<RET ={'ABC'},PRICE={" >0"},WEEK_END_DATE = {"=$(>Date(WeekEnd(Today(),-1)-70) < Date(WeekEnd(Today(),-1)))"}>}

PART_B_COUNT)

Might be pretty simple, but not sure what am I missing.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Hi,

I think you should combine the if with the set expression like this:

Sum({1<RET_NAME ={'ABC'},  WEEK_END_DATE = {">$(vWeekStartDate) < $(vWeekEndDate)"}>}

if(PART_SK ='1' or PART_SK ='2'  or PART_SK = '7'  or PART_SK ='6' or PART_SK ='0' or PART_SK ='5' or  PART_SK ='90' or  PART_SK ='79',PART_COUNT ))

In my previous post, my intention was that you create a tablebox with your fields incl. PART_COUNT (i.e. you might need to temorarily add some list boxes), then create list boxes for all the fields used in the set expression and select the set as used in your set expression. Then look what the resulting table box shows.

Maybe posting the app with scrambeld fields is possible?

Regards,

Stefan

View solution in original post

15 Replies
IAMDV
Luminary Alumni
Luminary Alumni

Hi Rohit,

You can get the Set Analysis working for the above situation. However, it will be easy to look at sample document. So please can you post the QV document?

Meanwhile, if you wanted to ignore all the selection for a chart. You need to use something like this....

=COUNT({$<[$(=Concat({1<$Field-={'Gender'}>}distinct $Field,'],[')&']=')>} Resp_ID)

In the above example, except Gender every field selection will be ignored. Hope this makes sense.

Cheers - DV

swuehl
MVP
MVP

Hi D V and arorarohit10,

if it's all about ignoring all selections except one field, I think something like this

=count{1<Gender= p(Gender) >} Resp_ID)

should work and might be easier to read after a while.

But I think there is more about it, without trying the code myself, try maybe soemthing like:

=Sum({1<RET ={'ABC'},

PART_SK  = {1,2,7,6,0,5,90,79},

WEEK_END_DATE = {">$(=Date(WeekEnd(Today(),-1)-70)) <$(=Date(WeekEnd(Today(),-1))))"}>}

PART_COUNT )

+ Sum({1<RET ={'ABC'},

PRICE={" >0"},

WEEK_END_DATE = {">$(=Date(WeekEnd(Today(),-1)-70)) <$(= Date(WeekEnd(Today(),-1))))"}>}

PART_B_COUNT)

Note how I changed the Seach string for the WEEK_END_DATE (putting the comparison out of the dollar sign expansion, changing to $(= and using two dollar sign expansions.

Not sure if this works or is all that needs to be done, though.

Ah, and take care that your WEEK_END_DATE matches the format of your dollar sign expansions.

Regards,

Stefan

Anonymous
Not applicable
Author

Hi,

Just try this Set analysis:

sum({1<RET_NAME = {'ABC'},PART_SK = {'1','2','7','6','0','5','90','79'},WEEK_END_DATE = {'>$(Date(WeekEnd(Today(),-1)-70))'}, WEEK_END_DATE = {'<$(Date(WeekEnd(Today(),-1)))'}>}PART_COUNT)

+

sum({1<RET_NAME = {'ABC'},PRICE = {'>0'},WEEK_END_DATE = {'>$(Date(WeekEnd(Today(),-1)-70))'}, WEEK_END_DATE = {'<$(Date(WeekEnd(Today(),-1)))'}>}PART_B_COUNT)

Not applicable
Author

**Please note that the list box has just the RET_NAME values.**

Thanks all for your valuable input. Here is what I was able to implement so far.

In the script section, declared the valiables for date calculations

LET vWeekStartDate = Date(weekend(Today(),-1)-70);

LET vWeekEndDate = Date(weekend(Today(),-1));

In the Expression of Line Chart

Sum({1<RET_NAME ={'ABC'},  WEEK_END_DATE = {">$(vWeekStartDate) < $(vWeekEndDate)"}, PART_SK  = {'1','2','7','6','0','5','90','79'}>} PART_COUNT )

If I just use first two conditions, it worsk fine ( the graph appears), as soon as I add the 'underlined' expressions, graph disappears. Looks like PART_SK expression is causing the issue. Not sure how it was working as mentioned above in the original post.

swuehl
MVP
MVP

Hi arorarohit,

can't see an issue with your expression (but maybe I'm just missing something), except that your original expression had two sums.

(second part like

+ Sum({1<RET ={'ABC'}, PRICE={" >0"}, WEEK_END_DATE = {">$(=Date(WeekEnd(Today(),-1)-70)) <$(= Date(WeekEnd(Today(),-1))))"}>}  PART_B_COUNT)

)

I assume you were just posting not the complete expression, but if you do, are you sure that the combinations of field selections really result in a value? Maybe, in your original version, you just got a result because of the second part?

Regards,

Stefan

Not applicable
Author

Thanks Stefan,

You are right on the part that I did not post complete expression in my second post. This is because (and I should have mentioned that before) that second part after '+' sign is giving values BUT right graph should be the combination of both first and second, and I am not getting any value out of first expression only. Where as the first expression was giving right results when the expression was as posted in the original post.

I am also not able to find the cause of problem with following conversion

AND  (PART_SK ='1' or PART_SK ='2'  or PART_SK = '7'  or PART_SK ='6' or PART_SK ='0' or PART_SK ='5' or  PART_SK ='90' or  PART_SK ='79') AND

to

PART_SK  = {'1','2','7','6','0','5','90','79'}


Thanks for help though, I guess I'll keep digging more.


swuehl
MVP
MVP

With this expression, do you get zero values in your line graph (you could also convert it to a straight table for examination) or something  like no data to display?

Could you post your app here at the forum (upload is available in advanced editor)?

Not applicable
Author

Thanks Stefan for your continious help.

I am getting 'No Data to display'.

I am sorry but I cannot upload my app as it is confidential data. The expression seems so straight forward, but I am not sure what exactly I am missing here.

Thanks again all for help.

swuehl
MVP
MVP

I think you already did, but could you try to select the assigned field values of your set expression in the appropriate field list boxes and look at the resulting record set regarding PART_COUNT?

What dimensions do you use in your line graph?

Could you post your data model here?

All this is kind of desperate trial, just have the impression that we are missing something obvious.

Regards,

Stefan