Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
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)
**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.
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
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.
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)?
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.
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