Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi guys,
I have created a line chart in qliksense. but I'm not getting how to write the percentages of measures marked just like below:
for me it's coming something like this
Can someone please guide me with this?
Thanks
Hi,
I'm not sure what your measures or dimensions are but the formula you need for the measure is like this:
sum(measure)/sum(total <dimension> measure).
The TOTAL key word tells Qlik to ignore all dimensions. By placing the dimension you're using between the angle brackets you're telling Qlik to not ignore that one dimension.
I hope this helps with your question.
Thanks
Anthony
Hi Anthony,
the expression isn't working. I'm a bit confused now..Could you kindly refer to the attached document and help with the measure's expression.
Hi,
From the picture you sent it looks like your numerator is either equal to/or very close to the denominator from March to June and then your numerator is much larger than your denominator.
Your set analysis for your numerator can be shortened. The monthstart( ) function has an optional parameter to shift the months so the addmonths( ) function isn't required. You should still be able to see the date it resolves to at the bottom of the screen.
(Count({<FACT_TYPE={"E-PAYMENT INVOICE"},
Period_Date={">=$(=monthstart(today(),-12))"},INV_YEAR=,INV_MONTH_EN=
>}Distinct TRANSACTION_NUMBER))
You've used double quotes in your string for FACT_TYPE. In Qlik this is used to resolve a function as you've done in Period_Date or to delineate a column name. Try changing this to single quotes.
Let me know if it's still not working and send over the denominator just in case.
Thanks
Anthony
When I'm trying to debug my syntax I create a table with your dimension and then add the numerator in one column and the denominator in the other. Just so I can see the numbers it comes up with.
Hi Anthony I have added the whole expression. kindly check the document.
Hi Serena,
By the looks of the output both your numerator and denominator are calculating but the numerator is not coming up with anything close to your denominator. I can see that you have a subset of Period_Date in the numerator but not the denominator. This could be pulling through a lot more invoices than expected so maybe try adding this to your denominator.
In your numerator you're counting distinct TRANSACTION_NUMBER and your denominator is counting NB_INVOICE. Given FACT_TYPE and Period_Date is a column that goes through both sets and you have a set analysis separating these it looks like the numerator is counting rows in your data that are not in the denominator. That's okay as long as there is a logical subset of your numerator and denominator.
Without seeing an example set of data can I suggest a way of debugging this code.
Start with a straight Count(distinct TRANSACTION_NUMBER) / count(NB_INVOICE) and add in one of your set analysis at a time. Start with the FACT_TYPE for both.
Regards
Anthony