topic Re: Monthwise Values Instead Of YTD in QlikView

Monthwise Values Instead Of YTD

ashish_2511 — Wed, 25 Nov 2020 16:16:04 GMT

Hi there

I've the following issue. Appreciate any quick help -

I've a pivot table like this -

Please Note-

The actual table in qlikview is actually a transpose of it where month-year is a dimension(The above mentioned table is not even the source, It's just a sample provided by client).

The data that comes from source are ytd values. Hence Feb-15 actuals would be Feb-15 data - Jan-15 data = 12883

Can anybody suggest how to achieve this at an expression level.

The Current expression that i'm using to calculate 'Connection Sales to Public' is - Only({<[Account Description] = {'412104*'}>}[YTD-Budget]). Need a modified expression instead though.

Regards

-Sneh

Re: Monthwise Values Instead Of YTD

sunny_talwar — Thu, 15 Sep 2016 17:13:01 GMT

What exactly are you trying to accomplish? I am not sure I understand how your data looks and what you are trying to achieve out of it. Can you may be provide a better sample with expected output?

Re: Monthwise Values Instead Of YTD

ashish_2511 — Thu, 15 Sep 2016 20:21:14 GMT

Dear Sunny

Thanks For your Quick response.

Hope this will help you understand the scenario better -

Source data comes from month-wise excel sheets, like one excel for one month.

Now I've a column called YTD Budget which gives me the numbers per month.

So, Now lets say January file has YTD Budget = 100 (From the excel) and Feb has 300.

In report I need to show

Month-Year YTD Budget

Jan-2016 100

Feb-2016 200(300-100)

Attached is a Reduced qvw. DM table is TB_Files.

Please see the Visualization table as well (There's just one).

Re: Monthwise Values Instead Of YTD

sunny_talwar — Thu, 15 Sep 2016 20:59:14 GMT

I think there is no data in the attached sample. It is all empty. Can you reattach with data in it

Re: Monthwise Values Instead Of YTD

neelamsaroha157 — Thu, 15 Sep 2016 21:18:44 GMT

If you have Month-Year as dimension(not pivoted) then you can use Your_Expression - Above(Your_Exp)

and if you are pivoting for Moth-Year Dim then use Your_Expression - Before(Your_Exp)

Re: Monthwise Values Instead Of YTD

Digvijay_Singh — Fri, 16 Sep 2016 03:57:39 GMT

You may think of calculating new month wise budget field in script using Peek/Previous function. Chart preparation would be easier.

Re: Monthwise Values Instead Of YTD

ashish_2511 — Fri, 16 Sep 2016 08:36:30 GMT

Dear Sunny

Please see this one.

Re: Monthwise Values Instead Of YTD

ashish_2511 — Fri, 16 Sep 2016 08:38:08 GMT

Could you please post an example.

Re: Monthwise Values Instead Of YTD

sunny_talwar — Fri, 16 Sep 2016 16:25:55 GMT

Are you looking to get this?

Expressions used

Only({<[Account Description] = {'412104*'}>}[YTD-Budget]) - Above(Only({<[Account Description] = {'412104*'}>}[YTD-Budget]))

Only({<[Account Description] = {'412105*'}>}[YTD-Budget]) - Above(Only({<[Account Description] = {'412105*'}>}[YTD-Budget]))

...

and so on

Re: Monthwise Values Instead Of YTD

ashish_2511 — Fri, 16 Sep 2016 17:04:17 GMT

Thanks a lot Sunny. this works for me.

I slightly modified it to -

If(RowNo()=1,Only({<[Account Description] = {'412104*'}>}[YTD-Budget]),Only({<[Account Description] = {'412104*'}>}[YTD-Budget]) - Above(Only({<[Account Description] = {'412104*'}>}[YTD-Budget])))

Which gives me the Jan-2015 value as well.

It would be great if you could also suggest a way to implement this at the script level.

Regards

-Sneh

Re: Monthwise Values Instead Of YTD

sunny_talwar — Fri, 16 Sep 2016 18:09:27 GMT

Your script is very complicated for me to propose anything, but as Digvijay mentioned, you can use Peek() or Previous() ? to accomplish this in the script. If you can provide sample data for one of the expressions, I might be able to show you how it can be done.

Also, you can further simplify your above expression to this

Only({<[Account Description] = {'412104*'}>}[YTD-Budget]) - Alt(Above(Only({<[Account Description] = {'412104*'}>}[YTD-Budget])), 0)

Instead of using this

Applied the new expression to just one expression.

Re: Monthwise Values Instead Of YTD

ashish_2511 — Fri, 16 Sep 2016 20:02:11 GMT

Thank you so much Sunny.

Regards

-Sneh