topic Need help recreating excel formula in QlikView chart expression in QlikView

Need help recreating excel formula in QlikView chart expression

bgreennc — Mon, 15 Jul 2019 05:02:36 GMT

Hello Qlik community,

I hope someone can help me with a QlikView chart expression. I have an excel sheet, with a chart that I am trying to recreate in QlikView. The name of the field that contains the data that I want to chart is: QualNotif. I tried using this formula, but it didn't work: RangeSum(above(count(distinct QualNotif),0,RowNo()))/RowNo().

Any help you can provide would be much appreciated.

Best Regards,

bgreennc

Re: Need help recreating excel formula in QlikView chart expression

Vegar — Mon, 15 Jul 2019 07:24:01 GMT

Your excel expression is not the same in the whole diagram. In the start it is Amount/RowLabel, but later it is (Amount+AnotherAmount)/RowLabel.

Could you please explain in words what the amounts are and what you are trying to calculate?

Re: Need help recreating excel formula in QlikView chart expression

bgreennc — Mon, 15 Jul 2019 12:34:38 GMT

Hello,

This excel sheet is supposed to be calculating a rolling 12 month for supplier issues. In my opinion, it is not a rolling 12 month. I didn't create the spreadsheet, or the formula, so I can't change them. I have been asked to recreate the existing excel formula / chart in QlikView.

The excel sheet is updated monthly. The sheet I put as an attachment is the latest version. It covers the time frame from July 2018 to June 2019. When the spreadsheet is updated next month, the time frame will be August 2018 to July 2019. In September, the time frame will be September 2018 to August 2019, etc.

When the calculated months are in the previous year, the formula is Amount/RowLabel. When the calculated months are in the current year, the formula is 'December from last year' + 'Month' / 'RowLabel'. For example, the Jan 2019 calculation would be (Dec 2018 + Jan 2019) / Row label.

If you have further questions, please let me know.

Thanks,

bgreennc

Re: Need help recreating excel formula in QlikView chart expression

Brett_Bleess — Wed, 07 Aug 2019 17:50:42 GMT

Billy,

It might help on this one if you can attach what you have in the way of your app, that way folks can see the data model and everything to try to piece together what is missing in this case. I suspect that may be why you have not gotten a further response at the moment. This will kick it back up into the current posts though, so maybe someone will see it and be able to add something extra without the app.

About the best I have otherwise is to maybe search out on the Design Blog area:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

You might be able to find some things there that may be helpful, there are hundreds of posts mostly related to how-to scenarios.

Regards,
Brett