topic Re: MTD Expression in QlikView

MTD Expression

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

Anyone can tell me that is below code is right for MTD expression

SUM({$<FinancialYear=, Quarter=,FiscalYear={$(=max(FiscalYear))}

,[PostingDate]={">=$(=MonthStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[PostingDate])))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[PostingDate]))"}

>}Target )

Thanks

Re: MTD Expression

Kushal_Chawda — Tue, 12 Apr 2016 07:08:13 GMT

Expression looks correct to me. Are you facing any issue?

Re: MTD Expression

Kushal_Chawda — Tue, 12 Apr 2016 07:11:43 GMT

or this will be sufficient, make sure that your [PostingDate] is in proper date format.

SUM({$<FinancialYear=, Quarter=,FiscalYear={"$(=max(FiscalYear))"},

[PostingDate]={">=$(=monthstart(max([PostingDate])))<=$(=max([PostingDate]))"}>}Target)

Re: MTD Expression

Gysbert_Wassenaar — Tue, 12 Apr 2016 07:25:00 GMT

Looks ok, but you may have to format the dates too:

SUM({$<FinancialYear=, Quarter=,FiscalYear={$(=max(FiscalYear))}

,[PostingDate]={">=$(=Date(MonthStart(max([PostingDate]))))<=$(=Date(max([PostingDate])))"}

>}Target )

Re: MTD Expression

ahmar811 — Tue, 12 Apr 2016 07:27:29 GMT

Please try this expression for MTD

SUM({$<FinancialYear=, Quarter=,FiscalYear={$(=max(FiscalYear))},

[PostingDate]={">=$(=MonthStart(max([PostingDate])))<=$(=MonthEnd(max([PostingDate])))"}>}Target )

Hope this help you

Thanks & Regards

Ahmar Ansari

Re: MTD Expression

prma7799 — Tue, 12 Apr 2016 07:33:25 GMT

am not getting proper targets in using this expression .

Re: MTD Expression

prma7799 — Tue, 12 Apr 2016 07:36:17 GMT

Hi gwassenaar‌,

Can you please look here

https://community.qlik.com/thread/212452

Thanks

Re: MTD Expression

Anonymous — Tue, 12 Apr 2016 07:36:51 GMT

might be helpful for you?

Set Analysis for certain Point in Time

Re: MTD Expression

sunny_talwar — Tue, 12 Apr 2016 07:38:10 GMT

I would take this another step by adding the formatting of PostingDate to the left hand side of the equation:

Sum({$<FinancialYear=, Quarter=, FiscalYear = {$(=Max(FiscalYear))}, [PostingDate] ={">=$(=Date(MonthStart(Max([PostingDate])), 'PostingDateFormatHere'))<=$(=Date(max([PostingDate]), 'PostingDateFormatHere'))"}>} Target)

Re: MTD Expression

prma7799 — Tue, 12 Apr 2016 07:39:31 GMT

Re: MTD Expression

prma7799 — Tue, 12 Apr 2016 07:42:01 GMT

Hi sunindia‌ , gwassenaar‌ , All

MTD expression is ok but my Target table is not link properly...

Please help me on this I stuck on this from long time and unable to understand how to do...

Thanks

Re: MTD Expression

sunny_talwar — Tue, 12 Apr 2016 08:34:01 GMT

How do you think your target table should have been linked? I guess without seeing a sample, it would be difficult to say what is wrong. Can you share a sample?

Re: MTD Expression

prma7799 — Tue, 12 Apr 2016 08:58:55 GMT

sunindia‌

Please find the attached ....

Re: MTD Expression

Gysbert_Wassenaar — Tue, 12 Apr 2016 09:08:29 GMT

Your Targets table contains only 1 Div_Code value and it's a different value then you have in your fact table. They should be the same if you want to be able to use the Country and Target value for anything.

Re: MTD Expression

prma7799 — Tue, 12 Apr 2016 09:17:09 GMT

I have multiple(38) div code in target and all div code present in GL entry table I just debugged that file with 1 line that y its showing 1 div code...

Can you please give me solution for how can i link target table with entire data model

Re: MTD Expression

Gysbert_Wassenaar — Tue, 12 Apr 2016 09:18:31 GMT

I don't understand what you want. Your Targets table is linked to the entire model. What do you expect?

Re: MTD Expression

prma7799 — Tue, 12 Apr 2016 09:26:41 GMT

Means I want to proper linking for target table .

If my target table is properly link then y month wise targets is not showing properly.

my expected output is if i select month APR then it will show me 933859.595959596 this value for div code 2180 and country united kingdom but unfortunately its showing different values .

please see my excel file ....