Advanced function subquery in master query (bug?) and filling blanks untill value is changed

Richard M shared this question ago

Answered

Hi there,

Yes another question. Two this time actually. (The organisation I started working at uses Yellowfin and nobody is an expert on it thus all the questions from my side)

Just to have a basic idea about what I am working with: I am trying to make an event chart where Numbers ordered is my metric and Price rise, Price drop and Not in inventory are the events (I need binary values for this).

First question is related to filling 'blanks' untill a value is changed. Attached below is my data. As you can see price information is not available for every date (as its from a different data source which only collects sales information, I used a subquery).

I would like to fill in the blanks with the price UNTIL it changes price, then it should start filling the blanks with the changed price. I need this because I want to calculate the Delta (see column Delta of price), so that I can give a 1 to my price rise column if the delta is higher than 0 and a 1 in my price drop column if it's lower than 0. I need to know when the price has changed as an event. How do I fill in the blanks untill the value changes?

My second question is related to this. Like I said I have a subquery and a master query. In my subquery I have calculated the Delta of price. In my master query I have made a calculated field called Copy of delta of price (see below, sorry it's in Dutch) where I want to see my subquery Delta of price so I can use it for another calculated field Price rise and Price drop.

/AxpNhVWHcHp3AAAAAElFTkSuQmCC

However, as you can see below my calculated field (Copy of delta of price) in my master query simply shows the Price and not the advanced function I have used on it. What is going on here? (Bug?)

All help is really appreciated, really enjoy the BI tool so far. Hope my situation and the questions are clear.

If there is another way you can recommend to get the Price rise and Price drop in binary shape I am ofcourse happy to hear that.

The same question

Replies (2)

Guest ●

Hi Richard,

Regarding your 1st question about how to fill blanks until the value changes, I'm not a consultant, just a support guy so I'm not guaranteeing my solution is the best one, but nevertheless it is the way I would tackle the problem. I would change the report into a View by using the "Use As View" feature, then I would create a Freehand SQL Report based on that view, and the SQL for that report would be something like what is in the following StackOverFlow post:

https://stackoverflow.com/questions/16669620/how-to-get-previous-value-for-null-values

(and you'll find plenty of other similar solutions on the internet).

Hope this suggestion helps you meet your report requirements, however, if you don't like it then please let me know and I can contact your account manager so they can organise Professional Services to build the report for you.

Regarding the 2nd issue about the copy of the Advanced Function, I have raised a Problem (10490) on your behalf so that we can track it separately there.

regards,

David

Reply URL

Richard M ●

Hi Dave,

Thanks for the clear answer. That should work, nice way to work around it I guess.

Regarding the second question I'll respond in the other topic.

Reply URL