My SET expression work well in QV , but in QS it does not accumulated.

[paulyeo11]

Hi All

My SET expression work well in QV , but in QS it does not accumulated.

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales)

I mean if i select Feb 2016 , it will display only feb 2016 sales 122K is worng.

it should display accumulated sales from jan till feb 2016 amount = 122 K + 18 K = 140 K

if i dont select any yr and mth , it display correct value.

Paul

[sunny_talwar · Accepted solution]

I think you have two month fields (month and Month) You were making selection in Month and setting the set analysis with month. So either ignore selection in Month or change the set analysis to Month.

This expression seems to work:

Sum({$<year = {$(=Max(year)-0)}, Month = {"<=$(Max({<year={$(=Max(year))}, sales = {'*'}>} Month))"}>}sales)

[paulyeo11]

hi sunny

thank you very much.  now my qv report very accurate

Paul

[paulyeo11]

Hi Sunny

By the way , may i know how you manage to know that why the month and Month cause the problem ? as in QV i never en-count any issue. look like QS are more sensitive.

Paul

[sunny_talwar]

Its the same in both QV and QS. I have seen your QV documents and I think you never use Month. You always use month and hence there was never an issue. If you make selection in your Month in QV, you will see the same issue.

[paulyeo11]

I just wake up wanted to change my Month to month, because all expression month i use month not Month , and i saw your reply. and now i notice that in QV i prefer month because month return number , and Month return Jan Feb. I just test and it work fine now.

So i do learn one thing after playing with QV , it is same as our work life , when we encounter problem , we need to break down the work into pcs , just like expression , break into step by step , this way any complicated issue sure can solve.

In the pass , i just focus on get the chart produce the number , recently i focus a lot of effort in make sure the number is correct and precise.

Now i can said i am fully understand the different between SET and YTD Flag, Now i also partly understand the different between QV and QS,

So my next task is clean up my variable in my Table model , and ColumnDim follow and duplicated field name like company COMPANY.....

[spsrk_84]

Hi Paul,

We tried the similar approach to get the cumulative value for month and unfortunately it's not working for us. Would you please attach the file for our reference.

Thanks,

Shiva

[paulyeo11]

Hi shivaram

I think it is better you attach your sample doc and ask the SET expert here to help you. As my sample doc is very complicated , will make you confuse.

Paul